数据库查询-连续出现的数字

（一）题目

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

（二）解题思路

普通查询

# Write your MySQL query statement below
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

效率有点低吧~要是连续至少n次的数字呢？若有别的有效率的方案，欢迎留言~

链表查询

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1 left join 
    Logs l2 on l1.Id = l2.Id - 1 left join 
    Logs l3 on l2.Id = l3.Id - 1
WHERE
    l1.Num = l2.Num
    AND l2.Num = l3.Num

lead 函数查询

SELECT DISTINCT
  NUM AS ConsecutiveNums
FROM
  (SELECT *,
   LEAD(NUM, 1) OVER(ORDER BY ID) AS L1,
   LEAD(NUM, 2) OVER(ORDER BY ID) AS L2
   FROM LOGS
  ) AS A
WHERE
  NUM = L1 AND NUM = L2;

知识点 

• lag  lead判断同一id同一列两行是否相等
• lag(exp_str,offset,defval) over(partition by ..order by …)
• lead(exp_str,offset,defval) over(partition by ..order by …)
• 其中exp_str是字段名（本例中为num），offset是偏移量（本例中为1），defval默认值可以不定义。
• over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
• 其参数：over（partition by columnname1 order by columnname2）
• 含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。