“SQL行列转换”的版本间的差异
跳到导航
跳到搜索
Jihongchang(讨论 | 贡献) |
Jihongchang(讨论 | 贡献) (→多科目) |
||
第226行: | 第226行: | ||
|0 | |0 | ||
|0 | |0 | ||
+ | |82 | ||
+ | |} | ||
+ | |||
+ | === 增加分组、聚合完整SQL === | ||
+ | <syntaxhighlight lang="sql"> | ||
+ | select sname as "姓名", | ||
+ | sum(case cname when '语文' then grade else 0 end) as "语文", | ||
+ | sum(case cname when '数学' then grade else 0 end) as "数学", | ||
+ | sum(case cname when '英语' then grade else 0 end) as "英语" | ||
+ | from score | ||
+ | group by sname | ||
+ | </syntaxhighlight> | ||
+ | {| class="wikitable" | ||
+ | !姓名 | ||
+ | !语文 | ||
+ | !数学 | ||
+ | !英语 | ||
+ | |- | ||
+ | |张三 | ||
+ | |80 | ||
+ | |85 | ||
+ | |81 | ||
+ | |- | ||
+ | |李四 | ||
+ | |77 | ||
+ | |90 | ||
+ | |69 | ||
+ | |- | ||
+ | |王五 | ||
+ | |91 | ||
+ | |60 | ||
|82 | |82 | ||
|} | |} |
2022年11月11日 (五) 06:50的版本
https://www.bilibili.com/video/BV1MF411z7X9
问题描述
学生成绩记录表包含以下信息:
sname | cname | grade |
---|---|---|
张三 | 语文 | 80 |
李四 | 语文 | 77 |
王五 | 语文 | 91 |
张三 | 数学 | 85 |
李四 | 数学 | 90 |
王五 | 数学 | 60 |
…… | …… | …… |
要求以每个学生一行数据的形式创建以下报表:
姓名 | 语文 | 数学 | 英语 |
---|---|---|---|
张三 | 80 | 85 | 81 |
李四 | 77 | 90 | 69 |
王五 | 91 | 60 | 82 |
建表
CREATE TABLE `score` (
`sname` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`cname` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`grade` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
插入测试数据
INSERT INTO `sql`.`score`
(`sname`,`cname`,`grade`)
VALUES
('张三','语文',80),
('李四','语文',77),
('王五','语文',91),
('张三','数学',85),
('李四','数学',90),
('王五','数学',60),
('张三','英语',81),
('李四','英语',69),
('王五','英语',82);
非指定科目显示分数为0
select sname as "姓名",
case cname when '语文' then grade else 0 end as "语文"
from score;
姓名 | 语文 |
---|---|
张三 | 80 |
李四 | 77 |
王五 | 91 |
张三 | 0 |
李四 | 0 |
王五 | 0 |
张三 | 0 |
李四 | 0 |
王五 | 0 |
非指定科目显示为NULL
select sname as "姓名",
case cname when '语文' then grade end as "语文"
from score;
姓名 | 语文 |
---|---|
张三 | 80 |
李四 | 77 |
王五 | 91 |
张三 | |
李四 | |
王五 | |
张三 | |
李四 | |
王五 |
多科目
select sname as "姓名",
case cname when '语文' then grade else 0 end as "语文",
case cname when '数学' then grade else 0 end as "数学",
case cname when '英语' then grade else 0 end as "英语"
from score;
姓名 | 语文 | 数学 | 英语 |
---|---|---|---|
张三 | 80 | 0 | 0 |
李四 | 77 | 0 | 0 |
王五 | 91 | 0 | 0 |
张三 | 0 | 85 | 0 |
李四 | 0 | 90 | 0 |
王五 | 0 | 60 | 0 |
张三 | 0 | 0 | 81 |
李四 | 0 | 0 | 69 |
王五 | 0 | 0 | 82 |
增加分组、聚合完整SQL
select sname as "姓名",
sum(case cname when '语文' then grade else 0 end) as "语文",
sum(case cname when '数学' then grade else 0 end) as "数学",
sum(case cname when '英语' then grade else 0 end) as "英语"
from score
group by sname
姓名 | 语文 | 数学 | 英语 |
---|---|---|---|
张三 | 80 | 85 | 81 |
李四 | 77 | 90 | 69 |
王五 | 91 | 60 | 82 |