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

博客围绕SQL查询中连续出现N次的问题展开,通过例题一查询至少成绩连续出现三次和例题二统计连续三次(及以上)为球队得分的球员名单,介绍了自关联和偏移函数lead等解题方法,总结指出此类问题主要考察窗口函数lag、lead的用法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

 

方法一:

【解题思路】

       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);

 

### SQL 查询字段中至少连续出现 N 的数字实现方式 #### 方法一:基于 `ROWNUM` 的 Oracle 解决方案 在 Oracle 数据库中,可以利用 `ROWNUM` 来标记每一行记录的位置,并通过排序来识别连续出现的数值。具体做法如下: 1. 首先按目标列(如 `num`)以及 `ROWNUM` 排序,生成一个新的序列号作为辅助列。 2. 计算每组连续值之间的差值,如果某个数连续,则其对应的差值会保持一致。 以下是具体的 SQL 实现: ```sql WITH numbered_rows AS ( SELECT num, ROWNUM AS row_num, ROW_NUMBER() OVER (ORDER BY ID) AS rn FROM lx_01 ), grouped_rows AS ( SELECT num, row_num - rn AS group_id FROM numbered_rows ) SELECT num, COUNT(*) AS count FROM grouped_rows GROUP BY num, group_id HAVING COUNT(*) >= :N; ``` 此方法的核心在于计算 `row_num - rn` 的差值,从而分组统计连续出现数[^1]。 --- #### 方法二:使用窗口函数 `LAG/LEAD` 对于更复杂的场景或者跨数据库兼容的情况,可以通过窗口函数 `LAG` 或 `LEAD` 来比较当前行与其前后的行是否相等。这种方法适用于大多数现代关系型数据库(如 MySQL 8.0+, PostgreSQL, SQL Server, Oracle 等)。 以下是一个通用的例子: ```sql WITH lagged_data AS ( SELECT Num, LAG(Num, 1) OVER (ORDER BY ID) AS prev_Num1, LAG(Num, 2) OVER (ORDER BY ID) AS prev_Num2 FROM logs ) SELECT DISTINCT Num FROM lagged_data WHERE Num = prev_Num1 AND Num = prev_Num2; ``` 该查询逻辑是通过对比当前行与前面两行的值是否相同,判断是否存在连续相同的数字。要扩展到任意长度 `N`,只需动态调整 `LAG` 函数中的偏移量即可[^2]。 --- #### 方法三:自定义变量法(MySQL 特定) 在某些不支持复杂窗口函数的老版本数据库(如早期版 MySQL),可借助用户定义变量完成类似功能。例如: ```sql SET @prev := NULL; SET @cnt := 0; SELECT Num, COUNT(*) AS freq FROM ( SELECT CASE WHEN @prev = Num THEN @cnt := @cnt + 1 ELSE @cnt := 1 END AS cnt_in_group, CASE WHEN @prev <> Num THEN @prev := Num END AS dummy, Num FROM logs ORDER BY ID ) t WHERE cnt_in_group >= :N GROUP BY Num; ``` 这里的关键点是对每迭代的结果进行累加计数,当遇到新值时重置计数器[^3]。 --- ### 总结 以上三种方法分别针对不同环境提供了解决方案。其中,第一种适合纯 Oracle 场景;第二种具有较好的跨平台适应能力;第三种则专用于旧版 MySQL 用户群。实际应用时需根据所使用的 DBMS 类型及其特性选择最合适的策略。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值