ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法
今天在使用多字段去重时,由于某些字段有多种可能性,只需根据部分字段进行去重,在网上看到了ROW_NUMBER()
OVER
(
PARTITION BY
PROJ_RID ORDER
BY
PROJ_RID )去重的方法,很不错,在此记录分享下:
ROW_NUMBER() OVER ( PARTITION BY PROJ_RID ORDER BY PROJ_RID) 表示根据PROJ_RID分组,在分组内部根据PROJ_RID排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.
ROW_NUMBER() OVER ( PARTITION BY PROJ_RID ORDER BY PROJ_RID) 表示根据PROJ_RID分组,在分组内部根据PROJ_RID排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的).
与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码.
EX:
SELECT T.*
(
SELECT ROW_NUMBER() OVER(PARTITION BY PPPC.PROJ_RID ORDER BY PPPC.PROJ_RID ) RN ,
FROM PPP_T_PROJ_PREPARE_CZCSNL PPPC
WHERE PAY_YEAR IS NOT NULL AND INVEST_EXPENSE IS NOT NULL
AND SUBSIDY_EXPENSE IS NOT NULL AND MATCH_EXPENSE IS NOT NULL
) T
WHERE T.RN=1
(
SELECT ROW_NUMBER() OVER(PARTITION BY PPPC.PROJ_RID ORDER BY PPPC.PROJ_RID ) RN ,
FROM PPP_T_PROJ_PREPARE_CZCSNL PPPC
WHERE PAY_YEAR IS NOT NULL AND INVEST_EXPENSE IS NOT NULL
AND SUBSIDY_EXPENSE IS NOT NULL AND MATCH_EXPENSE IS NOT NULL
) T
WHERE T.RN=1