编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/consecutive-numbers
解题:
判断条件,Num相同,并且id连续
可以查出,有没有连续的。
SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
上边的查询随便选一个,就是结果
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
也可以使用JOIN :
SELECT DISTINCT L1.Num
FROM Logs L1
JOIN Logs L2 ON L1.Id + 1 = L2.Id
JOIN Logs L3 ON L1.Id + 2 = L3.Id
WHERE L1.Num = L2.Num AND L1.Num = L3.Num
ORDER BY L1.Num
如果查询N个:
用到Case When else End语法。
SELECT DISTINCT Num
FROM (
SELECT Num,
CASE
WHEN @prev = Num THEN @count := @count + 1
WHEN (@prev := Num) IS NOT NULL THEN @count := 1
END CNT
FROM Logs, (SELECT @prev := NULL) X
ORDER BY Id
) AS A
WHERE A.CNT >= 3
相关知识:
可以在from表的地方跟几个同一张表,设置别名。
Case when语法用于解决数据库中有null,二查询结果又不想有null,替换为0之类的操作。也可以进行判断替换。
select *,
CASE WHEN name='null' THEN '名字为空'
else '名字不为空' end as st_name ,
case when sex = 1 then '男'
when sex = 0 then '女'
when sex = 2 then '未填' end as st_sex
from student;