侧边栏壁纸
博主头像
千异博主等级

学无止境,学以致用,志存高远!

  • 累计撰写 29 篇文章
  • 累计创建 26 个标签
  • 累计收到 0 条评论

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

千异
2022-04-19 / 0 评论 / 0 点赞 / 542 阅读 / 963 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-04-19,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。
  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);
  1. 插入数据
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);

数据插入之后两个表的内容如下:
image.png
image.png

  1. 查询
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;

0
博主关闭了所有页面的评论