求一个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
[解决办法]
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)
