/* 第一题 编写一个 SQL 查询，查找所有至少连续出现两次的数字。 Id Num 1 1 2 1

3 1

4 2

5 1

6 2

7 2

给定上面的 Logs 表， 1 和2是连续出现至少两次的数字

Num Times

1 3

2 2

考点：连续记录问题

*/ 数据准备 CREATE TABLE logs(

id INT ,

Num INT

)

INSERT INTO dbo.logs VALUES(1,1)

GO

INSERT INTO dbo.logs VALUES(2,1)

GO

INSERT INTO dbo.logs VALUES(3,1)

GO

INSERT INTO dbo.logs VALUES(4,2)

GO

INSERT INTO dbo.logs VALUES(5,1)

GO

INSERT INTO dbo.logs VALUES(6,2)

GO

INSERT INTO dbo.logs VALUES(7,2)

解题思路:

  1. 利用两个排序序号相减,相等的则为连续值,分别是按照ID排序标记序号和按照Num分组后按照ID排序标记序号 ```sql SELECT id,
    1. Num,
    2.       ROW_NUMBER() OVER (PARTITION BY Num ORDER BY id) rn1,
    3.       ROW_NUMBER() OVER (ORDER BY id) rn2,--这里之所以排序是因为id可能存在不连续的情况
    4. ROW_NUMBER() OVER (PARTITION BY Num ORDER BY id) - ROW_NUMBER() OVER (ORDER BY id) diff --用Num分组并按照ID排序标记序号与ROW_NUMBER() OVER (ORDER BY id)序号列相减,则相同数字的次数即为连续次数
    FROM dbo.logs
  1. ![image.png](https://cdn.nlark.com/yuque/0/2020/png/2586916/1602321073688-38cecc8c-5b1c-48b5-8e5a-490fb2177c5a.png#align=left&display=inline&height=215&margin=%5Bobject%20Object%5D&name=image.png&originHeight=215&originWidth=271&size=6406&status=done&style=none&width=271)
  3. 2. 再嵌套一层,根据diff一致的进行分组统计,计算出具体结果
  4. ```sql
  5. SELECT T.Num,
  6.        COUNT(1) Times
  7. FROM
  8. (
  9.     SELECT id,
  10.            Num,
  11.            ROW_NUMBER() OVER (PARTITION BY Num ORDER BY id) rn1,
  12.            ROW_NUMBER() OVER (ORDER BY id) rn2,
  13.            ROW_NUMBER() OVER (PARTITION BY Num ORDER BY id) - ROW_NUMBER() OVER (ORDER BY id) diff --用Num分组并按照ID排序标记序号与ROW_NUMBER() OVER (ORDER BY id)序号列相减,则相同数字的次数即为连续次数
  14.     FROM dbo.logs
  15. ) T
  16. GROUP BY T.Num,
  17.          diff
  18. HAVING  COUNT(1) >1
  19.          ;

image.png

其他解题思路

  1. MySQL

https://leetcode-cn.com/problems/consecutive-numbers/solution/bian-xie-yi-ge-sql-cha-xun-cha-zhao-suo-you-zhi-sh/

  1. SQL Server

https://leetcode-cn.com/problems/consecutive-numbers/solution/sql-server-jie-fa-by-neilsons/

  1. 连续时间拓展

    https://unote.uupt.com/note/Noteapi/shareDoc?doc_id=3195&doc_key=%252B3gzyWCL%252BAg%253D