大数据全系列 教程
1869个小节阅读:465.1k
目录
JAVA全系列 教程
面向对象的程序设计语言
Python全系列 教程
Python3.x版本,未来主流的版本
人工智能 教程
顺势而为,AI创新未来
大厂算法 教程
算法,程序员自我提升必经之路
C++ 教程
一门通用计算机编程语言
微服务 教程
目前业界流行的框架组合
web前端全系列 教程
通向WEB技术世界的钥匙
大数据全系列 教程
站在云端操控万千数据
AIGC全能工具班
A A
White Night
采用union all的方式:
xxxxxxxxxx
select std, score as 'M', 0 as 'E', 0 as 'Z' from my_score where prj='M';
select std, 0 as 'M', score as 'E', 0 as 'Z' from my_score where prj='E';
select std, 0 as 'M', 0 as 'E', score as 'Z' from my_score where prj='Z';
UNION ALL查询
xxxxxxxxxx
select std, score as 'M', 0 as 'E', 0 as 'Z' from my_score where prj='M'
UNION ALL
select std, 0 as 'M', score as 'E', 0 as 'Z' from my_score where prj='E'
UNION ALL
select std, 0 as 'M', 0 as 'E', score as 'Z' from my_score where prj='Z';
执行结果如下:
xxxxxxxxxx
+------+------+------+------+
| std | M | E | Z |
+------+------+------+------+
| S1 | 100 | 0 | 0 |
| S1 | 0 | 98 | 0 |
| S1 | 0 | 0 | 80 |
| S2 | 87 | 0 | 0 |
| S2 | 0 | 88 | 0 |
| S2 | 0 | 0 | 89 |
+------+------+------+------+
求和:
xxxxxxxxxx
select tmp.std, sum(tmp.M), sum(tmp.E), sum(tmp.Z)
from (
select std, score as 'M', 0 as 'E', 0 as 'Z' from my_score where prj='M'
UNION ALL
select std, 0 as 'M', score as 'E', 0 as 'Z' from my_score where prj='E'
UNION ALL
select std, 0 as 'M', 0 as 'E', score as 'Z' from my_score where prj='Z'
) tmp group by tmp.std;
执行后:
xxxxxxxxxx
+------+------------+------------+------------+
| std | sum(tmp.M) | sum(tmp.E) | sum(tmp.Z) |
+------+------------+------------+------------+
| S1 | 100 | 98 | 80 |
| S2 | 87 | 88 | 89 |
+------+------------+------------+------------+