sql题目：如何查找第N高数据、行列互换问题、连续出现N次问题

一、如何查找第N高数据

一、题目：

　　现在有“课程表”，记录了学生选修课程的名称以及成绩。现在需要找出语文课中成绩第二高的学生成绩。如果不存在第二高成绩的学生，那么查询应返回 null。

二、解题思路：

1、先找出所有选修了“语文”课的学生成绩

2、查找语文课程成绩的第二名：考虑到成绩可能有一样的值，所以使用distinct 成绩进行成绩去重

三、解题方案

1、核心：max(列名) 可以返回该列的最大值

　　使用子查询找出语文成绩查询最大的成绩记为a，然后再找出小于a的最大值就是课程成绩的第二高值

select max(distinct 成绩) 
from 成绩表
where 课程='语文' and
      成绩 < (select max(distinct 成绩) 
              from 成绩表 
              where 课程='语文');

2、核心：使用 limit 和 offset

　　limit n 子句表示查询结果返回前n条数据

　　offset n 表示跳过n条语句

　　limit y offset x 分句表示查询结果跳过 x 条数据，读取前 y 条数据

-- 使用limit和offset，降序排列再返回第二条记录可以得到第二大的值。
select distinct 成绩  
from 成绩表
where 课程='语文'
order by 课程,成绩 desc
limit 1,1;

3、核心：ifnull(a,b)函数

　　题目要求，如果没有第二高的成绩，返回空值，所以这里用判断空值的函数（ifnull）函数来处理特殊情况。对于本题的sql就是：

select ifnull(第2步的sql,null) as '语文课第二名成绩';

二、行列互换问题

一、问题：

　　将上面的 cook 表的数据转换为下面这样的数据。

二、解题思路：

1、输出行列互换的表结构

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

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。

　　第二行是2009年 2月份（m2）的值，而其他三列的值为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 年;

　　这个sql的运行过程如下

　　以此类推，这样我们就得到了目标表(行列互换)。

三、解决方案：遇到行列互换的问题，可以用下面的万能模版来解决。

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;

三、连续出现N次问题

一、问题：下面是学生的成绩表（表名score，列名：学号、成绩），使用SQL查找所有至少连续出现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

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

3、还要让这3个学号连续的人“成绩相等”，这个条件如下：a.成绩 = b.成绩 and 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.成绩
   and b.成绩 = c.成绩;

四、考点

1、本题考察的是连续出现，会有同学忽略“连续”二字

2、考察对自关联的灵活应用

3、从题目连续3次成绩相等，判断出“成绩相等”和“学号连续”这2个条件。考察构建“连续学号成绩相等”的思维构建能力

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