LeetCode-180. 连续出现的数字( Consecutive Numbers)。_leetcode python 180连续出现的数字-优快云博客

编写一个 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;