WITH TEST AS ( SELECT 1 AS A,4 AS B FROM DUAL UNION SELECT 1 AS A,5 AS B FROM DUAL UNION SELECT 1 AS A,6 AS B FROM DUAL UNION SELECT 2 AS A,7 AS B FROM DUAL UNION SELECT 2 AS A,8 AS B FROM DUAL UNION SELECT 2 AS A,9 AS B FROM DUAL UNION SELECT 3 AS A,10 AS B FROM DUAL UNION SELECT 3 AS A,11 AS B FROM DUAL ) SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) AS C FROM TEST
CREATE TABLE A2 ( CC VARCHAR2(2 CHAR), DD VARCHAR2(2 CHAR) );INSERT INTO A2 VALUES ('1','4'); INSERT INTO A2 VALUES ('1','5'); INSERT INTO A2 VALUES ('1','6'); INSERT INTO A2 VALUES ('2','7'); INSERT INTO A2 VALUES ('2','8'); INSERT INTO A2 VALUES ('2','9'); INSERT INTO A2 VALUES ('3','10'); INSERT INTO A2 VALUES ('3','11'); SELECT CC,DD ,RANK()OVER(partition by CC order by DD ) FROM A2 如果实现连续排序如下SELECT CC,DD ,DENSE_RANK()OVER(partition by CC order by DD ) FROM A2
SQL> select a,b,rank() over(partition by a order by b) c from test;A B C - -- ---------- 1 4 1 1 5 2 1 6 3 2 7 1 2 8 2 2 9 3 3 10 1 3 11 2
(
SELECT 1 AS A,4 AS B FROM DUAL UNION
SELECT 1 AS A,5 AS B FROM DUAL UNION
SELECT 1 AS A,6 AS B FROM DUAL UNION
SELECT 2 AS A,7 AS B FROM DUAL UNION
SELECT 2 AS A,8 AS B FROM DUAL UNION
SELECT 2 AS A,9 AS B FROM DUAL UNION
SELECT 3 AS A,10 AS B FROM DUAL UNION
SELECT 3 AS A,11 AS B FROM DUAL
)
SELECT A,
B,
ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) AS C
FROM TEST
( CC VARCHAR2(2 CHAR),
DD VARCHAR2(2 CHAR)
);INSERT INTO A2 VALUES ('1','4');
INSERT INTO A2 VALUES ('1','5');
INSERT INTO A2 VALUES ('1','6');
INSERT INTO A2 VALUES ('2','7');
INSERT INTO A2 VALUES ('2','8');
INSERT INTO A2 VALUES ('2','9');
INSERT INTO A2 VALUES ('3','10');
INSERT INTO A2 VALUES ('3','11');
SELECT CC,DD ,RANK()OVER(partition by CC order by DD ) FROM A2 如果实现连续排序如下SELECT CC,DD ,DENSE_RANK()OVER(partition by CC order by DD ) FROM A2
- -- ----------
1 4 1
1 5 2
1 6 3
2 7 1
2 8 2
2 9 3
3 10 1
3 11 2
顶一下,正解。
因为这里对于相同的A,B的值都不一样,所以这里使用row_number()与rank()效果一样
FROM tab d