SQL——连续出现的数字

哦啦哆啦

1. SQL三个排序函数 ROW_NUMBER()、RANK()、DENSE_RANK()

ROW_NUMBER()不并列 连续的
RANK()分组不连续排序（跳跃排序）
DENSE_RANK()并列连续
创建实例表：
实例表代码

1. DROP table IF EXISTS con;
2. Create Table IF NOT EXISTS con(id int,Num int);
3. INSERT INTO con VALUES(1,1);
4. INSERT INTO con VALUES(2,1);
5. INSERT INTO con VALUES(3,1);
6. INSERT INTO con VALUES(4,4);
7. INSERT INTO con VALUES(5,2);
8. INSERT INTO con VALUES(6,2);
9. INSERT INTO con VALUES(7,3);
10. INSERT INTO con VALUES(8,3);

复制代码

实例表：

三个函数 ROW_NUMBER()、 RANK()、 DENSE_RANK() 的区别如下图：

1. SELECT *,
2.         ROW_NUMBER() over(ORDER BY Num DESC) AS 'ROW_NUMBER()',
3.         RANK() over(ORDER BY Num DESC) AS 'RANK()',
4.         DENSE_RANK() over(ORDER BY Num DESC) AS 'DENSE_RANK()'
5. FROM con;

复制代码

函数区别结果：

2. TOP N 问题

返回第二高的数字

1. -- 方法一：DENSE_RANK() 窗口函数
2. SELECT DISTINCT Num
3. FROM (
4.         SELECT *,
5.                 DENSE_RANK() over(ORDER BY Num) AS dense
6.         FROM con
7. )  AS a
8. WHERE dense = 2;
10. -- 方法二： 相关子查询
11. SELECT DISTINCT Num
12. FROM con AS c1
13. WHERE 2 = (
14.         SELECT COUNT(DISTINCT Num)
15.         FROM con AS c2
16.         WHERE c1.Num >= c2.Num
17. )

复制代码

结果如下：

3. 返回连续出现  N 次的数

返回连续出现三次的数字

• 情况一：要求 id 连续，而不是表格中的原顺序 的情况下Num连续
  

1. -- 方法一： join on
2. SELECT DISTINCT c1.Num
3. FROM con AS c1
4. JOIN con AS c2 ON c1.Num = c2.Num AND c2.id = c1.id + 1
5. JOIN con AS c3 ON c3.Num = c2.Num AND c3.id = c2.id + 1
6. -- 方法二：in
7. SELECT DISTINCT Num
8. SELECT *
9. FROM con
10. WHERE (id + 1, Num) IN (SELECT * FROM con)
11.         AND (id + 2, Num) IN (SELECT * FROM con)

复制代码

结果如下：

• 情况二：按 表格中的行顺序  Num连续
  

1. -- 方法一：排序函数
2. SELECT DISTINCT Num
3. FROM (
4.         SELECT *,
5. --         id 索引值
6.         ROW_NUMBER() over(ORDER BY id) AS number,
7. --         分组排序
8.         DENSE_RANK() over(PARTITION BY Num ORDER BY id) AS dense,
9. --         差值
10.         ROW_NUMBER() over(ORDER BY id) - DENSE_RANK() over(PARTITION BY Num ORDER BY id) AS different
11. FROM con
12. ) AS a
13. GROUP BY Num, different
14. HAVING count(*) >= 3
15. -- 方法二： 偏移函数
16. -- lead（）向上偏移
18. SELECT DISTINCT Num
19. FROM (
20.         SELECT *,
21. --          按照表格中的原顺序， num连续出现相同值
22.                 LEAD(Num, 1) over(ORDER BY id) AS l1,
23.                 LEAD(Num,2) over(ORDER BY id) AS l2
24.         FROM con
25. ) AS a
26. WHERE Num = l1 AND l1 = l2;
27. -- lag() 向下偏移
28. SELECT DISTINCT Num
29. FROM (
30.         SELECT *,
31.         --         按照表格中的原顺序， num连续出现相同值
32.                 LAG(Num, 1) over(ORDER BY id) AS l1,
33.                 LAG(Num, 2) over(ORDER BY id) AS l2
34.         FROM con
35. ) AS a
36. WHERE Num = l1 AND l1 = l2;

复制代码

结果如下：

总结：

• TOP N 问题：排序函数 和 相关子查询方法
  
• 返回连续出现数字
  ①连续指的是 按表格中顺序连续出现，排序函数和偏移函数
  ②id连续，JOIN ON 和 IN 两种方法
  

来源:https://www.cnblogs.com/anran-0/p/18182493
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！