mysql 横表转竖表

最新推荐文章于 2023-08-05 15:53:51 发布

花道峰

最新推荐文章于 2023-08-05 15:53:51 发布

阅读量472

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/kai666ling/article/details/79000900

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

表结构，数据如下

1.SELECT * from test.t_subscore;
SELECT sname AS '姓名',
SUM(IF(cource = '语文', score, 0)) AS '语文',
SUM(IF(cource = '数学', score, 0)) AS '数学',
SUM(IF(cource = '物理', score, 0)) AS '物理',
SUM(score) AS '总分',
ROUND(AVG(score), 2) AS '平均分'
FROM test.t_subscore
GROUP BY sname;

结果如下：

2.SELECT sname AS '姓名',
SUM(IF(cource = '语文', score, 0)) AS '语文',
SUM(IF(cource = '数学', score, 0)) AS '数学',
SUM(IF(cource = '物理', score, 0)) AS '物理',
SUM(score) AS '总分',
ROUND(AVG(score), 2) AS '平均分'
FROM test.t_subscore
GROUP BY sname
UNION ALL
SELECT '总分', SUM(`语文`), SUM(`数学`), SUM(`物理`), SUM(`总分`), ROUND(avg(`平均分`), 2)
FROM (SELECT sname AS '姓名',
SUM(IF(cource = '语文', score, 0)) AS '语文',
SUM(IF(cource = '数学', score, 0)) AS '数学',
SUM(IF(cource = '物理', score, 0)) AS '物理',
SUM(score) AS '总分',
ROUND(AVG(score), 2) AS '平均分'
FROM test.t_subscore
GROUP BY sname) t2;