查看“SQL行列转换”的源代码

https://www.bilibili.com/video/BV1MF411z7X9

=== 问题描述 ===
学生成绩记录表包含以下信息：
{| 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
|}