题目描述

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

Id Num
1 1
2 1
3 1
4 2
5 1
6 2
7 2

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

ConsecutiveNums
1

相关知识

此题不需要前置知识

解题过程

  1. 分析题意

    • 声明两个变量
      • @last_num：上一个数字
      • @last_time：当前数字出现的次数
      • 当当前元组的数字与前一个元组的数字相同，则出现次数等于上一个元组数字出现次数加一
      • 当当前元组的数字与前一个元组的数字不同，则出现次数重置为1
    • 当连续出现3次时选择这个数，并去重

  2. 编写sql

    SELECT
    DISTINCT Num AS ConsecutiveNums
FROM
    (
        SELECT
            Num,
            (
                CASE
                WHEN @last_num=Num
                THEN @last_time:=@last_time+1
                WHEN (@last_num:=Num) OR (@last_num =0)
                THEN @last_time:=1
                END
            ) AS Continuettimes            
        FROM
            `Logs`,(SELECT @last_num:=NULL,@last_time:=0) t
    ) c
WHERE
    Continuettimes>2

  3. 提交答案，通过

  4. 查看题解，发现有个更简洁的写法

    • sql

      SELECT 
	DISTINCT (num) "ConsecutiveNums"
FROM
	(
        SELECT 
        	Num,
        	(row_number() over(order by id ) - row_number() over(partition by num order by id)) rank_
		FROM 
        	Logs
	) tmp
group by rank_,Num
having count(rank_)>=3;

    • 首先通过ROW_NUMBER()获取使用id排序的行号，解决id不连续的问题(id 1,2,3,100,101,102,…..)

    • 然后通过ROW_NUMBER()获取根据Num分组后每个分组内通过id排序获得的行号

    • 将两个行号对于相减，如果它们是相邻的，那么相邻的第一个行号会增加1，相邻的第二个行号也会增加1，相减后结果是相等的，即这个连续数字串的起始行号

    • 由于可能会有重复，所以使用DISTINCT去重

    • 最后，通过num和order共同分组，使用count进行统计,由于count函数执行在where之后所以此时进行筛选需要使用having筛选出需要的num

学习总结

  1. 变量运算符

    • 变量在进行计算赋值时使用的运算符是:=
    • 变量在进行比较运算时使用的运算符是=

  2. SQL的执行顺序

    1. 执行FROM
    2. WHERE条件过滤
    3. GROUP BY分组
    4. 执行SELECT投影列，聚集函数
    5. HAVING条件过滤
    6. 执行ORDER BY 排序

      这也是为什么在WHERE语句中声明的变量可以在SELECT中使用