oracle - 排序后分配行号

Question

排序后，如何为我的代码分配行号？

我使用了 ROWNUM，但结果未按4,3,2,1,8,7,5,6如下顺序排列。

ROWNUM C0   M     SS 
------ --- ----- -----                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  4    a   ace    11                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  3    a   abe     7                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  2    a   abd     3                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  1    a   a__    21                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  8    b   ace    11                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  7    b   abe     7                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  6    b   abd     3                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  5    b   a__    21 

我的代码：

CREATE TABLE T4 (
C0 VARCHAR2(10),
C1 VARCHAR2(10),
C2 NUMBER
);
INSERT INTO T4  VALUES ('a','abd',1);
INSERT INTO T4  VALUES ('a','abd',2);
INSERT INTO T4  VALUES ('a','abe',3);
INSERT INTO T4  VALUES ('a','abe',4);
INSERT INTO T4  VALUES ('a','ace',5);
INSERT INTO T4  VALUES ('a','ace',6);

INSERT INTO T4  VALUES ('b','abd',1);
INSERT INTO T4  VALUES ('b','abd',2);
INSERT INTO T4  VALUES ('b','abe',3);
INSERT INTO T4  VALUES ('b','abe',4);
INSERT INTO T4  VALUES ('b','ace',5);
INSERT INTO T4  VALUES ('b','ace',6);



SELECT ROWNUM,rr.C0,rr.M, rr.ss -- How to modify to get 1,2,3,4,5,6,7,..
FROM
(
SELECT C0,C1 M, SUM(C2) ss FROM T4  GROUP BY C0, C1
UNION
SELECT C0,SUBSTR(C1,1,1)||'__'  , SUM(C2) ss FROM T4  GROUP BY C0,SUBSTR(C1,1,1)) rr
ORDER BY rr.C0,rr.M DESC;

标签： oraclerownum

Answer 1

将 rownum 移到查询之外：

select rownum, r.* from (
... your query with order by
) r