이를 위해 UNION ALL을 사용하십시오.
먼저 테이블을 생성해 보겠습니다.
mysql> create table DemoTable729 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), MySQLMarks int, CMarks int, JavaMarks int ); Query OK, 0 rows affected (0.40 sec)
삽입 명령을 사용하여 테이블에 일부 레코드 삽입:
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Chris',94,67,75);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Robert',45,99,54);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('David',57,89,43);
Query OK, 1 row affected (0.12 sec) select 문을 사용하여 테이블의 모든 레코드 표시:
mysql> select *from DemoTable729;
그러면 다음과 같은 출력이 생성됩니다. -
+-----------+-------------+------------+--------+-----------+ | StudentId | StudentName | MySQLMarks | CMarks | JavaMarks | +-----------+-------------+------------+--------+-----------+ | 1 | Chris | 94 | 67 | 75 | | 2 | Robert | 45 | 99 | 54 | | 3 | David | 57 | 89 | 43 | +-----------+-------------+------------+--------+-----------+ 3 rows in set (0.00 sec)
다음은 단일 열에 학생 점수를 표시하는 쿼리입니다.
mysql> select StudentId,StudentName,'MySQL' as SubjectName, MySQLMarks as Score from DemoTable729 UNION ALL select StudentId,StudentName, 'C' as SubjectName, CMarks as Score from DemoTable729 UNION ALL select StudentId,StudentName, 'Java' as SubjectName, JavaMarks as Score from DemoTable729;
그러면 다음과 같은 출력이 생성됩니다. -
+-----------+-------------+-------------+-------+ | StudentId | StudentName | SubjectName | Score | +-----------+-------------+-------------+-------+ | 1 | Chris | MySQL | 94 | | 2 | Robert | MySQL | 45 | | 3 | David | MySQL | 57 | | 1 | Chris | C | 67 | | 2 | Robert | C | 99 | | 3 | David | C | 89 | | 1 | Chris | Java | 75 | | 2 | Robert | Java | 54 | | 3 | David | Java | 43 | +-----------+-------------+-------------+-------+ 9 rows in set (0.00 sec)