求一个MYSQL下数据转置问题
我有一数据表:
id, student, course, mark
1, 张三, 语文, 90
2, 李四, 语文, 80
3, 张三, 数学, 70
4, 李四, 数学, 80
现在想用转置表的方法转成二维表输出,要求格式是:
student, 语文, 数学
张三, 90, 70
李四, 80, 80
我用下面的查询语句却不成功,但是在MSSQL下却可以通过,请问在MYSQL下要怎么修改啊?
select student,
sum(case course when ‘语文’ then mark end) as 语文,
sum(case course when ‘数学’ then mark end) as 数学
from table1 group by student
-
SQL code
mysql> CREATE TABLE student -> ( -> ID INT, -> student VARCHAR(20) CHARACTER SET UTF8, -> course VARCHAR(20) CHARACTER SET UTF8, -> mark INT -> ) ENGINE=MYISAM CHARACTER SET UTF8; Query OK, 0 rows affected (0.08 sec) mysql> mysql> INSERT student VALUES(1,'张三','语文',90); Query OK, 1 row affected (0.00 sec) mysql> INSERT student VALUES(2,'李四','语文',80); Query OK, 1 row affected (0.00 sec) mysql> INSERT student VALUES(3,'张三','数学',70); Query OK, 1 row affected (0.00 sec) mysql> INSERT student VALUES(4,'李四','数学',80); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT -> student, -> SUM(CASE WHEN course = '语文' THEN mark ELSE 0 END) AS 语文, -> SUM(CASE WHEN course = '数学' THEN mark ELSE 0 END) AS 数学 -> FROM student -> GROUP BY student; +---------+------+------+ | student | 语文 | 数学 | +---------+------+------+ | 张三 | 90 | 70 | | 李四 | 80 | 80 | +---------+------+------+ 2 rows in set (0.00 sec) mysql> mysql> SELECT -> student, -> SUM(IF(course='语文',mark,0)) AS 语文, -> SUM(IF(course='数学',mark,0)) AS 数学 -> FROM student -> GROUP BY student; +---------+------+------+ | student | 语文 | 数学 | +---------+------+------+ | 张三 | 90 | 70 | | 李四 | 80 | 80 | +---------+------+------+ 2 rows in set (0.00 sec) mysql> mysql> DROP TABLE student; Query OK, 0 rows affected (0.00 sec)