row_number() over() 行数递增
-
row_number() over() 和 rownum差不多,功能更强一点(可以在各个分组内从1开时排序).
-
转载:https://www.cnblogs.com/alsf/p/6344197.html
-
是一种窗口函数,用于为查询结果集中的每一行分配一个唯一的序号。
- 分组聚合,分组排序
- 它可以在ORDER BY子句中使用,以便按特定列对结果集进行排序。
row_number() over(partition by [要分组的列] order by [排序列])
-
over()里头的 分组以及排序的执行 晚于
- where
- group by
- order by 的执行。
-
partition by 用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,它和聚合函数不同的地方在于
- **它能够返回一个分组中的多条记录,**而聚合函数一般只有一个反映统计值的记录。
一个SQL整理
- PARTITION BY 后加 1级分组,2级分组
- row_num > 1
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY DATE(created_at),
HOUR(created_at) ORDER BY created_at
) AS row_num
FROM your_table
) AS subquery
WHERE row_num <= 10;
DATE()
HOUR()
- 分解
1 zhangsan 1
2 lisi 22
3 lisi 33
SELECT
*,
ROW_NUMBER() OVER ( PARTITION BY username ORDER BY info DESC )
FROM USER
-- 结果。info 倒序排,然后变成 row_num
3 lisi 33 1
2 lisi 22 2
1 zhangsan 1 1
- 条件整理
相等
PARTITION BY username ORDER BY info DESC
PARTITION BY username ORDER BY username, info DESC --普通情况,这样写即可。
PARTITION BY username, username ORDER BY info DESC --容易误导人的写法
-- 加了 逗号,第一个只能升序。 添加了 ORDER BY 后,可以倒序
Row_Number() OVER 案例
SELECT empno,WORKDEPT,SALARY,
Row_Number() OVER (partition by workdept ORDER BY salary desc) rank
-- 根据 workdept工作 分组,然后根据 薪资倒序排序。
FROM employee
--------------------------------------
000010 A00 152750 1
000110 A00 66500 2
000120 A00 49250 3
200010 A00 46500 4
200120 A00 39250 5
000020 B01 94250 1
000030 C01 98250 1
000130 C01 73800 2
(2)对查询结果进行排序:(无分组)
SELECT empno,WORKDEPT,SALARY,
Row_Number() OVER (ORDER BY salary desc) rank
-- 直接倒序排序
FROM employee
--------------------------------------
000010 A00 152750 1
000030 C01 98250 2
000070 D21 96170 3
000020 B01 94250 4
000090 E11 89750 5
000100 E21 86150 6
000050 E01 80175 7
000130 C01 73800 8
000060 D11 72250 9
rank() over() 跳跃排序
2、rank() over()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内).
rank
英
/ræŋk/
n.
军衔,军阶;(社会、团体中的)地位,等级;(尤指较高的)地位;
v.
把……分等级,属于某等级;<美>级别高于,地位高于;排列,使排成行
adj.
(植被)茂密的,芜生蔓长的;难闻的,恶臭的;(强调质量、状况等)极端的,糟糕的
select workdept,salary,
rank() over(partition by workdept order by salary) as dense_rank_order
-- 按照部门分组,然后按照 薪资排序
from emp order by workdept;
------------------
A00 39250 1
A00 46500 2
A00 49250 3
A00 66500 4
A00 152750 5
B01 94250 1
C01 68420 1
C01 68420 1
C01 73800 3
dense_rank() over() 连续排序
3、dense_rank() over()是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 .
dense
英
/dens/
adj.
稠密的;浓密的,浓重的;密度大的;(文字)难懂的;愚钝的,笨拙的
select workdept,salary,
dense_rank() over(partition by workdept order by salary) as dense_rank_order
from emp
order by workdept;
------------------
A00 39250 1
A00 46500 2
A00 49250 3
A00 66500 4
A00 152750 5
B01 94250 1
C01 68420 1
C01 68420 1
C01 73800 2
C01 98250 3
用ROW_NUMBER删除重复数据
假设表TAB中有a,b,c三列,可以使用下列语句删除a,b,c都相同的重复行。
DELETE FROM
(
select year,QUARTER,RESULTS,
-- 分组和排序 和查出的值,都是 查出这3个字段。如果row_no不是 第一个就删除
row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO
FROM SALE )
WHERE ROW_NO>1