1、格式
row_number() over(partition by 列名1 order by 列名2 desc)
2、解析
表示根据 列名1 分组,然后在分组内部根据 列名2 排序,而此函数计算的值就表示每组内部排序后的顺序编号,可以用于去重复值
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.
3、实例
--分析函数
SELECT USER_NAME,
SCHOOL,
DEPART,
ROW_NUMBER() OVER(PARTITION BY USER_NAME ORDER BY SCHOOL, DEPART DESC)
FROM USER_M;
结果
--分析函数
SELECT *
FROM (SELECT USER_NAME,
SCHOOL,
DEPART,
ROW_NUMBER() OVER(PARTITION BY USER_NAME ORDER BY SCHOOL, DEPART DESC) RN
FROM USER_M)
WHERE RN = 1;
结果
--结合分页
SELECT *
FROM (SELECT A.USER_NAME,
A.SCHOOL,
A.DEPART,
ROW_NUMBER() OVER(PARTITION BY SCHOOL ORDER BY USER_NAME, DEPART DESC) RN
FROM (SELECT * FROM USER_M) A
WHERE ROWNUM <= 10)
WHERE RN >= 1;
结果