面试手写SQL - 求连续3天若干问题，终于会了！

前段时间秋招的时候小明去上海某公司面试，被搞懵了，面试官直接当场让其手写SQL，出的题目他当时也懵了，题目听起来不难，但是面试现场给你出题，那种紧张的氛围下是不容易想到的，于是小明课下自己补了作业！

面试题目：写出连续三天成绩大于等于60分的学生姓名？

问题分析：

这个题目关键点是如何把连续三天表示出来，这里要在思维上转换一下，不然是很难做出来的，说到底还是SQL写的有点少了。来看问题，连续三天，就是日期连续，如果给你一张表，我们确实很快地从中找出来连续合格的学生，原因只是我们大脑对表很熟悉了，但是让你用程序写出来的话，会发现思维突然卡顿了，所以关键点是把我们从表上读数据的思维程序化。给你一张表，你会先根据某个学生定位到该学生第一次成绩合格的那行，然后根据当前行的日期的下一天去找该学生的成绩有没有合格，如果合格那么继续去看下一天的是否合格，如果有三天连续合格的，那么我们就得到我们想要的结果了，嗯，我们的思维是这样的，我们程序也应该这样，那么来把我们的这个思维程序化一下，关键点是SQL怎么判断是否有下一天呢，没错，你想到了，exists,这个SQL很强大的功能能帮助我们实现，下面我们来撸代码！

1.建表

create table student (
       id varchar2(20) primary key,
       name varchar2(20),
       rq date,
       score number(10)
       )

2.插入实验数据

insert into student values('1','菜鸟1号',to_date('2021-11-01','YYYY-MM-DD'),60);
insert into student values('2','菜鸟1号',to_date('2021-11-02','yyyy-mm-DD'),67);
insert into student values('3','菜鸟1号',to_date('2021-11-03','YYYY-MM-DD'),90);
insert into student values('13','菜鸟1号',to_date('2021-11-04','YYYY-MM-DD'),90);
insert into student values('4','菜鸟2号',to_date('2021-11-01','YYYY-MM-DD'),50);
insert into student values('5','菜鸟2号',to_date('2021-11-02','YYYY-MM-DD'),60);
insert into student values('6','菜鸟2号',to_date('2021-11-03','YYYY-MM-DD'),70);
insert into student values('7','菜鸟3号',to_date('2021-11-01','YYYY-MM-DD'),50);
insert into student values('8','菜鸟3号',to_date('2021-11-02','YYYY-MM-DD'),45);
insert into student values('9','菜鸟3号',to_date('2021-11-03','YYYY-MM-DD'),90);
insert into student values('10','菜鸟4号',to_date('2021-11-01','YYYY-MM-DD'),100);
insert into student values('11','菜鸟4号',to_date('2021-11-02','YYYY-MM-DD'),100);
insert into student values('12','菜鸟4号',to_date('2021-11-03','YYYY-MM-DD'),100);

3.得到我们待查询的表
在这里插入图片描述 4.SQL实现

select distinct name from student t1 where 
exists (select 1 from student t2 where t2.rq = t1.rq+1 and t2.name = t1.name 
and 
exists (select 1 from student t3 where t3.rq= t2.rq+1 and t3.name = t2.name)) and score >=60

用distinct是因为SQL在进行日期判断查找的时候，会从每个日期开始都去找一下有没有连续的天数，因为菜鸟一号是连续四天合格，上面如果不加distinct的话会出现两个菜鸟一号，我们只需要查出合格的学生就行了，所以要去重一下。

5.执行结果

在这里插入图片描述
确实写完后，很简单，但这不是我们想要的，想像一下，每个日期都去判断的话，是不是特别慢，所以我们要优化，这是我们必须要考虑的，所以重点来了！！！

下面介绍一个SQL中特别重要也特别好用的函数 row_number() over()，窗口函数，有了这个函数，你就可以对分组内的数据进行排序，下面对我们上面的SQL进行优化改写，这里对日期连续处理有个小技巧，就是将日期减去每个排序号，正好是一样的值，记住这一点就好做了。

row_number() over()

语法格式：row_number() over(partition by 分组列 order by 排序列 desc)

partition by是对字段进行分组，和group by 一样可以对某些字段进行分组，但和group by 后面使用 order by不同的是，partition by 后面可以使用 order by对分组内进行排序，然后使用row_number()返回排序号。

关于OVER开窗函数更多用法可以参考一篇文章：https://blog.51cto.com/u_15057820/2650448，看完就恍然大悟了。

1.对学生进行分组，分组内按日期排序

select s.*,row_number() over(partition by name order by rq) rn from student s where score >=60

在这里插入图片描述
2.筛选出日期连续合格的学生（关键）

select name, count(1) as day from
(
select s.*,row_number() over(partition by name order by rq) rn from student s where score >=60
) t
group by name,rq-rn

在这里插入图片描述
3.过滤连续三天的数据

select name, count(1) as day from
(
select s.*,row_number() over(partition by name order by rq) rn from student s where score >=60
) t
group by name,rq-rn  having count(1) >=3