图解SQL面试题 （学习笔记） 003多表查询----3

行列互换问题，怎么办？

【题目】
下面是表名为 cook 的表

要求查询结果如下：

叫做行列互换问题，就是将一维表转化为二维表。

1. 输出行列互换的表结构
   可以看出，需要输出的有 5 列，其中只有 “年” 这一列是表 cook 中原有的，其他 4 列（也就是 2-5 列：m1 对应的是 1 月份、m2 对应的是 2 月份、m3 对应的是 3 月份、m4 对应的是 4 月份）需要自己创建。

select 年,m1,m2,m3,m4
from cook;

可以看出查询结果和目标表的列名结构一样，但是 2-5 列（m1、m2、m3、m4）的值不是题目要求的：

2.如何将 2-5 列的值替换成对应的值？
可以用 case 语句进行条件判断来替换。

年份和月份匹配，则为对应值，不匹配则为 0。

select 年,
(case 月 when '1' then 值 else 0 end) as m1,
(case 月 when '2' then 值 else 0 end) as m2,
(case 月 when '3' then 值 else 0 end) as m3,
(case 月 when '4' then 值 else 0 end) as m4
from cook;

在这个查询结果中，每一行表示某年某月的某个值。比如第一行是 2009 年 1 月份（m1）的值，而其他三列 m2、m3、m4 的值为 0。

又向目标接近了一步，但是多出来的 0 值，怎么办？
3.去掉 0 值，简化表格的行数
可以使用分组汇总来实现。按 “年” 分组（group by），然后用汇总函数（max）取出每组非零的值 (也就是这个案例中的某年某月对应的数值)。

select 年,
max(case 月 when '1' then 值 else 0 end) as 'm1',
max(case 月 when '2' then 值 else 0 end) as 'm2',
max(case 月 when '3' then 值 else 0 end) as 'm3',
max(case 月 when '4' then 值 else 0 end) as 'm4'
from cook
group by 年;

这样我们就得到了目标表 (行列互换)。
【本题考点】
1.考查用 case 语句进行数据替换，和条件判断
.遇到行列互换的问题，可以用下面的万能模版来解决。

select A,
-- 第2步，在行列互换结果表中，其他列里的值分别使用case和max来获取
max(case B when 'm' then C else 0 end) as 'm',
max(case B when 'n' then C else 0 end) as 'n'
from cook
-- 第1步，在行列互换结果表中按第1列分组
group by A;

【举一反三】
下面是学生的成绩表 (表名：成绩表，列名：学号，课程，成绩)

使用 sql 语句实现将该表行转化为下面的表结构：

select 学号,max(case when 课程='语文' then 成绩 else 0 end) as 语文成绩,max(case when 课程='数学' then 成绩 else 0 end) as 数学成绩 from 成绩表
group by 学号

找出连续出现 N 次的内容？

【题目】
下面是学生的成绩表（表名 score，列名：学号、成绩），使用 SQL 查找所有至少连续出现 3 次的成绩。

例如，“成绩” 这一列里 84 是连续出现 3 次的成绩。
1.条件 1：什么是连续出现 3 次？
假设 “学号” 是按顺序排列的（如果不是，可以增加一列，这一列是按序号顺序排列的），所以每一学号与上一学号相差 1。例如下图的 3 个学号是连续学号，他们之间的关系是：
某一学号（0002）= 下一位的学号（0003）-1
下一位学号（0003）= 下下位学号（0004）-1

2.条件 2：成绩相等
如果这 3 个连续学号的成绩相等，就是题目要求的 “至少连续出现 3 次的成绩”。

3.利用 “自关联 “的思路
自连接（自身连接）的本质是把一张表复制出多张一模一样的表来使用。
SQL 语法：
select 列名
from 表名 as 别名1,表名 as 别名2;

步骤 1）将成绩表（score）复制 3 个一样的表，分别命名为 a、b、c

select *  from score as a, score as b, score as c

步骤 2）我们需要找到这 3 个表中 3 个连续的学号，这个条件如下
a. 学号 = b. 学号 - 1 and b. 学号 = c. 学号 - 1

select *  from score as a, score as b, score as c
where a.学号=b.学号-1 and b.学号=c.学号-1

步骤 3）还要让这 3 个学号连续的人 “成绩相等”，这个条件如下
a. 成绩 = b. 成绩 and b. 成绩 = c. 成绩
将步骤 2 和步骤 3 的条件合并起来就是下面 SQL 里的 where 字句：

select *  from score as a, score as b, score as c
where a.学号=b.学号-1 and b.学号=c.学号-1 and
a.成绩=b.成绩=c.成绩

步骤 4）前面步骤已经将连续 3 人相等的成绩找出，现在用 distinct 去掉自连接产生的重复数。最终 SQL 如下：

select distinct a.成绩 as 最终答案
 from score as a, 
 score as b, 
 score as c
where a.学号=b.学号-1 and b.学号=c.学号-1 and
a.成绩=b.成绩=c.成绩

本题考察的是连续出现，会有同学忽略 “连续” 二字
• 考察对自关联的灵活应用
• 从题目连续 3 次成绩相等，判断出 “成绩相等” 和“学号连续”这 2 个条件。考察构建 “连续学号成绩相等” 的思维构建能力

【举一反三】
遇到类似 “连续出 N 次的问题” 可以回想本题的解答思路，如：查询至少连续 3 天没有出勤的员工。