题目描述:
表:Logs
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示:
示例 1:
输入: Logs 表: +----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ 输出: Result 表: +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ 解释:1 是唯一连续出现至少三次的数字。
解题思路:
1、笛卡尔积,按照num自关联三次,在where条件中筛选id递增的记录。
select distinct t1.num as ConsecutiveNums
from logs t1,logs t2,logs t3
where t1.id = t2.id - 1
and t2.id = t3.id - 1
and t1.num = t2.num
and t2.num = t3.num;
但这类解法的缺陷是只适用于数量较小和求连续次数较小的情况,一旦数据量增多,查询时间急剧上升。且如果求num连续出现100次,就得自关联100次,SQL书写冗长,对查询时间也是巨大考验。
2、用row_number()函数按照num进行分组,按照id进行排序,增加一列rn,通过观察可以得出数字连续出现时,id-rn的值是不变的。
select distinct num as ConsecutiveNums
from (
select id+1 as id, num,
row_number() over(partition by num order by id) as rn
from logs
) t
group by (t.id-t.rn),num having count(*)>=3;
id+1是为了防止id从0开始,导致id-rn的值小于0,会报错:“BIGINT UNSIGNED value is out of range in '(`t`.`id` - `t`.`rn`)'”