编写一个 SQL 查询, 查找所有至少连续出现三次的数字.
- +----+-----+
- | Id | Num |
- +----+-----+
- | 1 | 1 |
- | 2 | 1 |
- | 3 | 1 |
- | 4 | 2 |
- | 5 | 1 |
- | 6 | 2 |
- | 7 | 2 |
- +----+-----+
例如, 给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字.
- +-----------------+
- | ConsecutiveNums |
- +-----------------+
- | 1 |
- +-----------------+
这一题存在比较麻烦的两点:
1, 判断该数字连续出现次数大于三次.
2, 记录连续出现次数大于三次的数字.
整理下大概思路:
首先统计每个数字出现次数, 默认出现次数为 1, 遇到相同则 + 1, 遇到不同则重置, 做一轮查询, 这样就能查出所有数字的连续出现最大值, 然后再做一次查询即可
- SELECT DISTINCT
- al.Num AS ConsecutiveNums
- FROM
- (SELECT
- @count := @count + (
- CASE
- WHEN @num = e.Num
- THEN 0
- ELSE 1
- END
- ) AS c,
- e.Num,
- @num := e.Num
- FROM
- LOGS e,
- (SELECT
- @count := 0) a,
- (SELECT
- @num := - 1) b) al
- GROUP BY al.c,
- al.num
- HAVING COUNT(*)>= 3
来源: https://www.cnblogs.com/linux-java/p/9765178.html