SQL——连续出现的数字

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;

函数区别结果:

1. 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
)

结果如下:

2. 返回连续出现 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;

结果如下:

总结:

  1. TOP N 问题:排序函数 和 相关子查询方法
  2. 返回连续出现数字
    ①连续指的是 按表格中顺序连续出现,排序函数和偏移函数
    ②id连续,JOIN ONIN 两种方法

热门相关:我真不是开玩笑   新书   春秋我为王   终极高手   一世倾心:误惹腹黑师弟