在MySQL的基础上，在单列中显示学生成绩？
为此，请使用UNIONALL。
让我们首先创建一个表：
create table DemoTable729 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), MySQLMarks int, CMarks int, JavaMarks int );
使用insert命令在表中插入一些记录：
insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Chris',94,67,75);
insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('Robert',45,99,54);
insert into DemoTable729(StudentName,MySQLMarks,CMarks,JavaMarks) values('David',57,89,43);使用select语句显示表中的所有记录：
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)
以下是在单个列中显示学生成绩的查询：
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)