mysql中查询连续出现N次的问题

例题一：查询至少成绩连续出现三次

 

方法一：

【解题思路】

       1.条件1：什么是连续出现3次？

        学号连续，且分数相等

        假设“学号”是按顺序排列的（如果不是，可以增加一列，这一列是按序号顺序排列的），所以 每一学号与上一学号相差1。例如下图的3个学号是连续学号，他们之间的关系是：

        2.条件2：成绩相等

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

        3.利用“自关联“的思路

        自连接（自身连接）的本质是把一张表复制出多张一模一样的表来使用。

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

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

 

        步骤3）还要让这3个学号连续的人“成绩相等”，这个条件如下

        a.成绩 = b.成绩 and b.成绩 = c.成绩

        将步骤2和步骤3的条件合并起来就是下面SQL里的where字句：

select *
from score as a,
   score as b,
   score as c
 where a.学号 +1 = b.学号
   and b.学号 +1 = c.学号
   and 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.学号 +1 = b.学号
   and b.学号 +1 = c.学号
   and a.成绩 = b.成绩
   and b.成绩 = c.成绩;

方法二：

        步骤一）：使用偏移函数 lead(...) over(...),添加辅助列，作为临时表

        注意：lead(property,num,default)有三个参数，第一个参数「property」标识想查询的列，「num」标识相对于当前行的第num行（可以理解为滑动的行数），第三个参数是默认值。

select *,lead(成绩,1)over(order by 学号 ) as 成绩1,lead(成绩,2)over(order by 学号 ) as 成绩2
from 成绩表；

         步骤二）：将临时表作为子表添加条件筛选查询

select  成绩
from(
select *,lead(成绩,1)over(order by 学号 ) as 成绩1,lead(成绩,2)over(order by 学号 ) as 成绩2
from `成绩表`) as a  
where 成绩=a.成绩1 and a.成绩1=a.成绩2；

 

例题二：请你写一个sql语句统计出，连续三次（及以上）为球队得分的球员名单

         步骤一）：添加两个辅助列 lead () over(),每一次将球员姓名上移一行，作为临时表

 

select *,lead(球员姓名,1)over( order by 得分时间) as 球员姓名1,
        lead(球员姓名,2)over( order by 得分时间) as 球员姓名2
from 分数表

         步骤2：对第一步得到的临时表加条件限制，查询结果

select 球员姓名
from(
select *,lead(球员姓名,1)over( order by 得分时间) as 球员姓名1,lead(球员姓名,2)over( order by 得分时间) as 球员姓名2
from 分数表) as a
where 球员姓名=球员姓名1 and 球员姓名1=球员姓名2；

 总结：

查询中连续出现N次的查询问题，组要考察窗口函数lag、lead的用法。

这两个函数一般用于计算差值，例如：

1. 计算花费时间。例如：某数据是每个用户浏览网页的时间记录，将记录的时间错位之后，进行两列相减就可以得到每个用户浏览每个网页实际花费的时间。时间（连续N次，连续登陆N天，时隔多久）

2. 计算与上次相比薪水涨幅。

3. 连续出现N次的万能模板

 

-- 查询模板
select distinct 列1
from(
select 列1,
lead(列1,1) over(order by 序号) as 列2,
lead(列1,2) over(order by 序号) as 列3,
...
lead(列1,n-1) over(order by 列) as 列n,
from 表名
) as a
where (a.列1 = a.列2 and ... and a.列1 = a.列n);