“SQL行列转换”的版本间的差异

来自姬鸿昌的知识库
跳到导航 跳到搜索
 
（未显示同一用户的10个中间版本）
第3行： 第3行：
 
=== 问题描述 ===
 
=== 问题描述 ===
 
学生成绩记录表包含以下信息：
 
学生成绩记录表包含以下信息：
 +
{| class="wikitable"
 +
!sname
 +
!cname
 +
!grade
 +
|-
 +
|张三
 +
|语文
 +
|80
 +
|-
 +
|李四
 +
|语文
 +
|77
 +
|-
 +
|王五
 +
|语文
 +
|91
 +
|-
 +
|张三
 +
|数学
 +
|85
 +
|-
 +
|李四
 +
|数学
 +
|90
 +
|-
 +
|王五
 +
|数学
 +
|60
 +
|-
 +
|……
 +
|……
 +
|……
 +
|}
 +
 +
 +
要求以每个学生一行数据的形式创建以下报表：
 +
{| class="wikitable"
 +
!姓名
 +
!语文
 +
!数学
 +
!英语
 +
|-
 +
|张三
 +
|80
 +
|85
 +
|81
 +
|-
 +
|李四
 +
|77
 +
|90
 +
|69
 +
|-
 +
|王五
 +
|91
 +
|60
 +
|82
 +
|}
 +
 +
==== 建表 ====
 +
<syntaxhighlight lang="sql">
 +
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;
 +
</syntaxhighlight>
 +
 +
 +
 +
==== 插入测试数据 ====
 +
<syntaxhighlight lang="sql">
 +
INSERT INTO `sql`.`score`
 +
(`sname`,`cname`,`grade`)
 +
VALUES
 +
('张三','语文',80),
 +
('李四','语文',77),
 +
('王五','语文',91),
 +
('张三','数学',85),
 +
('李四','数学',90),
 +
('王五','数学',60),
 +
('张三','英语',81),
 +
('李四','英语',69),
 +
('王五','英语',82);
 +
</syntaxhighlight>
 +
 +
 +
 +
==== 非指定科目显示分数为0 ====
 +
<syntaxhighlight lang="sql">
 +
select sname as "姓名",
 +
case cname when '语文' then grade else 0 end as "语文"
 +
from score;
 +
</syntaxhighlight>
 +
{| class="wikitable"
 +
!姓名
 +
!语文
 +
|-
 +
|张三
 +
|80
 +
|-
 +
|李四
 +
|77
 +
|-
 +
|王五
 +
|91
 +
|-
 +
|张三
 +
|0
 +
|-
 +
|李四
 +
|0
 +
|-
 +
|王五
 +
|0
 +
|-
 +
|张三
 +
|0
 +
|-
 +
|李四
 +
|0
 +
|-
 +
|王五
 +
|0
 +
|}
 +
 +
 +
 +
 +
==== 非指定科目显示为NULL ====
 +
<syntaxhighlight lang="sql">
 +
select sname as "姓名",
 +
case cname when '语文' then grade end as "语文"
 +
from score;
 +
</syntaxhighlight>
 +
{| class="wikitable"
 +
!姓名
 +
!语文
 +
|-
 +
|张三
 +
|80
 +
|-
 +
|李四
 +
|77
 +
|-
 +
|王五
 +
|91
 +
|-
 +
|张三
 +
|
 +
|-
 +
|李四
 +
|
 +
|-
 +
|王五
 +
|
 +
|-
 +
|张三
 +
|
 +
|-
 +
|李四
 +
|
 +
|-
 +
|王五
 +
|
 +
|}
 +
 +
==== 多科目 ====
 +
<syntaxhighlight lang="sql">
 +
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;
 +
</syntaxhighlight>
 +
{| class="wikitable"
 +
!姓名
 +
!语文
 +
!数学
 +
!英语
 +
|-
 +
|张三
 +
|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 ===
 +
<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
 +
|}
 +
又因为对于分组、聚合函数（group by、sum） INT 类型字段值 NULL 和 0 是一样的，所以也可以简写成：<syntaxhighlight lang="sql">
 +
select sname as "姓名",
 +
sum(case cname when '语文' then grade end) as "语文",
 +
sum(case cname when '数学' then grade end) as "数学",
 +
sum(case cname when '英语' then grade end) as "英语"
 +
from score
 +
group by sname
 +
</syntaxhighlight>得到的查询结果是一样的。

2022年11月11日 (五) 06:51的最新版本

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

又因为对于分组、聚合函数（group by、sum） INT 类型字段值 NULL 和 0 是一样的，所以也可以简写成：

select sname as "姓名",
sum(case cname when '语文' then grade end) as "语文",
sum(case cname when '数学' then grade end) as "数学",
sum(case cname when '英语' then grade end) as "英语"
from score
group by sname

得到的查询结果是一样的。

取自“https://www.jihongchang.top/index.php?title=SQL行列转换&oldid=3435”