SQL——连续出现的数字
1. SQL三个排序函数 ROW_NUMBER()、RANK()、DENSE_RANK()ROW_NUMBER()不并列 连续的
RANK()分组不连续排序（跳跃排序）
DENSE_RANK()并列连续
创建实例表：
实例表代码DROP table IF EXISTS con;
Create Table IF NOT EXISTS con(id int,Num int);
INSERT INTO con VALUES(1,1);
INSERT INTO con VALUES(2,1);
INSERT INTO con VALUES(3,1);
INSERT INTO con VALUES(4,4);
INSERT INTO con VALUES(5,2);
INSERT INTO con VALUES(6,2);
INSERT INTO con VALUES(7,3);
INSERT INTO con VALUES(8,3);实例表：
三个函数 ROW_NUMBER()、 RANK()、 DENSE_RANK() 的区别如下图：
SELECT *,
ROW_NUMBER() over(ORDER BY Num DESC) AS 'ROW_NUMBER()',
RANK() over(ORDER BY Num DESC) AS 'RANK()',
DENSE_RANK() over(ORDER BY Num DESC) AS 'DENSE_RANK()'
FROM con;函数区别结果：
2. TOP N 问题
返回第二高的数字
-- 方法一：DENSE_RANK() 窗口函数
SELECT DISTINCT Num
FROM (
SELECT *,
DENSE_RANK() over(ORDER BY Num) AS dense
FROM con
)AS a
WHERE dense = 2;
-- 方法二： 相关子查询
SELECT DISTINCT Num
FROM con AS c1
WHERE 2 = (
SELECT COUNT(DISTINCT Num)
FROM con AS c2
WHERE c1.Num >= c2.Num
)结果如下：
3. 返回连续出现N 次的数
返回连续出现三次的数字
[*]情况一：要求 id 连续，而不是表格中的原顺序 的情况下Num连续
-- 方法一： join on
SELECT DISTINCT c1.Num
FROM con AS c1
JOIN con AS c2 ON c1.Num = c2.Num AND c2.id = c1.id + 1
JOIN con AS c3 ON c3.Num = c2.Num AND c3.id = c2.id + 1
-- 方法二：in
SELECT DISTINCT Num
SELECT *
FROM con
WHERE (id + 1, Num) IN (SELECT * FROM con)
AND (id + 2, Num) IN (SELECT * FROM con)结果如下：
[*]情况二：按 表格中的行顺序Num连续
-- 方法一：排序函数
SELECT DISTINCT Num
FROM (
SELECT *,
-- id 索引值
ROW_NUMBER() over(ORDER BY id) AS number,
-- 分组排序
DENSE_RANK() over(PARTITION BY Num ORDER BY id) AS dense,
-- 差值
ROW_NUMBER() over(ORDER BY id) - DENSE_RANK() over(PARTITION BY Num ORDER BY id) AS different
FROM con
) AS a
GROUP BY Num, different
HAVING count(*) >= 3
-- 方法二： 偏移函数
-- lead（）向上偏移
SELECT DISTINCT Num
FROM (
SELECT *,
-- 按照表格中的原顺序， num连续出现相同值
LEAD(Num, 1) over(ORDER BY id) AS l1,
LEAD(Num,2) over(ORDER BY id) AS l2
FROM con
) AS a
WHERE Num = l1 AND l1 = l2;
-- lag() 向下偏移
SELECT DISTINCT Num
FROM (
SELECT *,
-- 按照表格中的原顺序， num连续出现相同值
LAG(Num, 1) over(ORDER BY id) AS l1,
LAG(Num, 2) over(ORDER BY id) AS l2
FROM con
) AS a
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】 我们会及时删除侵权内容，谢谢合作！
页:
[1]