Mysql横表转纵表以及纵表转横表

1. 建表

CREATE TABLE marks (nickname VARCHAR (20),course VARCHAR (20),mark INT);
CREATE TABLE IF NOT EXISTS students (nickname VARCHAR (20),chinese INT,math INT,english INT);

2. 插入数据

INSERT INTO marks (nickname,course,mark) VALUES ('张三','语文',60),('张三','数学',70),('张三','英语',80),('李四','语文',90),('李四','数学',100);
INSERT INTO students (nickname,chinese,math,english) VALUES ('张三',60,70,80),('李四',90,100,0);

数据插入之后两个表的内容如下：

3. 查询

SELECT nickname,
	SUM(IF (course='语文',mark,0)) AS chinese,
	SUM(IF (course='数学',mark,0)) AS math,
	SUM(IF (course='英语',mark,0)) AS english
FROM marks GROUP BY nickname;

SELECT nickname,'语文' AS course,chinese AS mark FROM students UNION
SELECT nickname,'数学' AS course,math AS mark FROM students UNION
SELECT nickname,'英语' AS course,english AS mark FROM students;

--上面第二条SQL查询结果会多一条成绩为0的数据，使用下面的过滤
SELECT nickname,'语文' AS course,chinese AS mark FROM students where  chinese!=0 UNION ALL
SELECT nickname,'数学' AS course,math AS mark FROM students where  math!=0 UNION ALL
SELECT nickname,'英语' AS course,english AS mark FROM students where  english!=0  ORDER BY nickname,course DESC;