提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
一、题目
表:Logs
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示:
Logs 表:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Result 表:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
#1 是唯一连续出现至少三次的数字。
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/consecutive-numbers
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
二、解决方法
1.多表连接,id连续
① 连续出现的三个数-------> id相邻
② num值相等
③ 至少连续出现3次,也可能多次------>同一值三次以上重复也只取一次
# Write your MySQL query statement below
SELECT DISTINCT a.num as ConsecutiveNums
FROM Logs a,Logs b,logs c
WHERE a.id=b.id-1
AND b.id=c.id-1
AND a.num=b.num
AND b.num=c.num
注意
不可以使用
#因为a.num=b.num的值为布尔值0/1,
WHERE a.num=b.num=c.num
#等价于
where (a.num=b.num and 0=c.num) or (a.num=b.num and 1=c.num)
2.连续的数id不一定连续
(1)对数据进行编号
使用 row_number() over( order by ‘列名’) 对每一行编号
SELECT id,num,row_number() over(order by id) as SeriesID
FROM Logs
结果
输入:
{"headers": {"Logs": ["Id", "Num"]}, "rows": {"Logs": [[1, 1], [2, 1], [3, 1], [4, 2], [5, 1], [6, 2], [7, 2]]}}
输出:
{"headers": ["id", "num", "SeriesID"], "values": [[1, 1, 1], [2, 1, 2], [3, 1, 3], [4, 2, 4], [5, 1, 5], [6, 2, 6], [7, 2, 7]]}
(2)对原始数据进行分组编号
ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)
根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).
本题中,需要求连续相等的数,以num值为第一个分组值,id为第二个值
{"headers": ["id", "num", "SeriesID", "groupID"], "values": [[1, 1, 1, 1], [2, 1, 2, 2], [3, 1, 3, 3], [5, 1, 5, 4], [4, 2, 4, 1], [6, 2, 6, 2], [7, 2, 7, 3]]}
+----+-----+----------+-----------+
| Id | Num | SeriesID | groupID |
+----+-----+----------+----------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 2 |
| 3 | 1 | 3 | 3 |
| 5 | 1 | 5 | 4 |
| 4 | 2 | 4 | 1 |
| 6 | 2 | 6 | 2 |
| 7 | 2 | 7 | 3 |
+----+-----+----------+----------+
观察发现
(1)id为5的数和id为4的数交换位置
(2)如果想要找出连续的值
则,SeriesID-groupID的值必定是一个连续值
① 当num=n时,第一个开始的数SeriesID为 m ,groupID 为1
第二个连续的数SeriesID为 m +1,groupID 为1+1
最后一个连续的数SeriesID为m+s,groupID为s
所以,差值永远相等
(3) 做差值
{"headers": ["id", "num", "diff"], "values": [[1, 1, 0], [2, 1, 0], [3, 1, 0], [5, 1, 1], [4, 2, 3], [6, 2, 4], [7, 2, 4]]}
+----+-----+-------+
| Id | Num | diff |
+----+-----+------+
| 1 | 1 | 0 |
| 2 | 1 | 0 |
| 3 | 1 | 0 |
| 5 | 1 | 1 |
| 4 | 2 | 3 |
| 6 | 2 | 4 |
| 7 | 2 | 4 |
+----+-----+------+
(4)判断该数字出现3次,且如果出现3次及以上,仍然只算一个值
# Write your MySQL query statement below
SELECT distinct aa.num as ConsecutiveNums
FROM
(SELECT num,
(row_number() over(order by id) -
row_number() over(partition by num order by id)) as diff
FROM Logs) aa
GROUP BY aa.num,aa.diff
Having count(*)>=3
GROUP BY aa.num,aa.diff
Having count(*)>=3
多层分组,count(*), 对最内层分组进行聚合计算
>> {"headers": ["ConsecutiveNums", "count(*)"], "values":
[[1, 1], [1, 3], [2, 1], [2, 2]]}