我有一数据表:id, student, course,
1, 张三, 语文, 90
2, 李四, 语文, 80
3, 张三, 数学, 70
4, 李四, 数学, 80现在想用转置表的方法转成二维表输出,要求格式是:student, 语文, 数学
张三, 90, 70
李四, 80, 80我用下面的查询语句却不成功,但是在MSSQL下却可以通过,请问在MYSQL下要怎么修改啊?
select student,
sum(case course when '语文' then end) as 语文,
sum(case course when '数学' then end) as 数学
from table1 group by student
1, 张三, 语文, 90
2, 李四, 语文, 80
3, 张三, 数学, 70
4, 李四, 数学, 80现在想用转置表的方法转成二维表输出,要求格式是:student, 语文, 数学
张三, 90, 70
李四, 80, 80我用下面的查询语句却不成功,但是在MSSQL下却可以通过,请问在MYSQL下要怎么修改啊?
select student,
sum(case course when '语文' then end) as 语文,
sum(case course when '数学' then end) as 数学
from table1 group by student
select student,
sum(if(course='语文',,0)) as 语文,
sum(if(course='数学',,0)) as 数学
from table1
group by student
-> (
-> ID INT,
-> student VARCHAR(20) CHARACTER SET UTF8,
-> course VARCHAR(20) CHARACTER SET UTF8,
-> 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 ELSE 0 END) AS 语文,
-> SUM(CASE WHEN course = '数学' THEN 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='语文',,0)) AS 语文,
-> SUM(IF(course='数学',,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)