WITH T AS(SELECT 1 RN,10 ID FROM DUAL UNION ALL
SELECT 2,10 FROM DUAL UNION ALL
SELECT 3,20 FROM DUAL UNION ALL
SELECT 4,10 FROM DUAL UNION ALL
SELECT 5,10 FROM DUAL UNION ALL
SELECT 6,30FROM DUAL UNION ALL
SELECT 7,30 FROM DUAL UNION ALL
SELECT 8, 10
FROM DUAL)
SELECT RN, ID, DENSE_RANK() OVER(ORDER BY ID1)
FROM (SELECT RN,
ID,
DECODE(LEAD(ID) OVER(ORDER BY RN), ID, RN, RN - 1) ID1
FROM T) T;复制代码
|