MySql 连续出现的数字
约 443 字大约 1 分钟
2025-02-02
在 LeetCode 180. 连续出现的数字问题中，我们的目标是查询连续出现 3 次的数字。如果要扩展到 N 次，官方的sql显然不合理，那有没有通用的解放呢。
解题过程
- 由于题目未说明ID连续，可能存在ID不连续，因此需对结果集重新编号，以确保其连续。
SELECT id, num, row_number() OVER (ORDER BY id) AS `row_number`
FROM Logs| id | num | row_number |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 4 |
| 5 | 1 | 5 |
| 6 | 2 | 6 |
| 7 | 2 | 7 |
- 我们对num分组后排序，也就是计算出该数字是第几次出现。
SELECT id, num, row_number() OVER (PARTITION BY num ORDER BY id) AS sn
FROM Logs
ORDER BY id| id | num | sn |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 1 | 4 |
| 6 | 2 | 2 |
| 7 | 2 | 3 |
- 将上述两列（row_number 和 sn）相减，若结果相同，则表示连续；若不同，则表示不连续。
SELECT id, num,
row_number() OVER (ORDER BY id) AS `row_number`,
row_number() OVER (PARTITION BY num ORDER BY id) AS sn,
row_number() OVER (ORDER BY id) - row_number() OVER (PARTITION BY num ORDER BY id) AS `row_number - sn`
FROM Logs
ORDER BY id| id | num | row_number | sn | row_number - sn |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 0 |
| 2 | 1 | 2 | 2 | 0 |
| 3 | 1 | 3 | 3 | 0 |
| 4 | 2 | 4 | 1 | 3 |
| 5 | 1 | 5 | 4 | 1 |
| 6 | 2 | 6 | 2 | 4 |
| 7 | 2 | 7 | 3 | 4 |
- 将上述结果按
num和row_number - sn分组，计算num和count，即每个数字连续出现的次数。通过去重（distinct）和过滤后，即可得到最终结果。
SELECT DISTINCT num AS ConsecutiveNums
FROM (
SELECT num, row_number() OVER (ORDER BY id) - row_number() OVER (PARTITION BY num ORDER BY id) AS sub
FROM Logs
) t
GROUP BY num, sub
HAVING COUNT(1) >= 3;| ConsecutiveNums |
|---|
| 1 |