- 建表
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);
- 插入数据
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);
数据插入之后两个表的内容如下:
- 查询
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;