SQL> SQL> SELECT A, C, ROW_NUMBER() OVER(PARTITION BY MARK ORDER BY C DESC) 2 FROM (SELECT A, SUM(B) C, 'a' MARK FROM QZW GROUP BY A ORDER BY C DESC) 3 ;
A C ROW_NUMBER()OVER(PARTITIONBYMA -------- ---------- ------------------------------ 乙 13 1 甲 7 2 丙 4 3
SQL> drop table qzw;
Table dropped
说的有道里,那最好用dense_rank()SQL> select a,sum(b),dense_rank() over(order by sum(b) desc) rn 2 from test_06 3 group by a;A SUM(B) RN ---------- ---------- ---------- 乙 13 1 甲 7 2 丁 4 3 丙 4 3 戊 2 4
create table test_1(col1 varchar2(10),col2 int); insert into test_1(col1,col2) values('甲',5); insert into test_1(col1,col2) values('乙',3); insert into test_1(col1,col2) values('丙',4); insert into test_1(col1,col2) values('甲',2); insert into test_1(col1,col2) values('乙',10);select * from test_1;select col1,sum(col2) as col2,rank() over(order by sum(col2) desc) rn from test_1 group by col1;
哦,知道了dense_rank 与 rank 的区别
这个很单纯的sql直接sum再排序,更直观 select A,sum(B) GROUP BY A 而group 有隐含排序显示
---------- ----------
甲 5
甲 2
乙 3
乙 10
丙 4SQL> select a,sum(b),rank() over(order by sum(b) desc) rn
2 from test_06
3 group by a;A SUM(B) RN
---------- ---------- ----------
乙 13 1
甲 7 2
丙 4 3
from test_06
group by a这样也是可以的
(SELECT a,SUM(b) FROM test GROUP BY a) a
(A varchar2(10),
B number);select * from zzw_temp3 for update;update zzw_temp3
set a=trim(a),b=trim(b);
commit;select a.*,rownum "排名" from (
select a,sum(b) from zzw_temp3
group by a
order by sum(b) desc) a;这样就可以解决了
看lz需要什么样的排序了,ls两种方法已经实现了
A B
-------- ----------
甲 5
乙 3
丙 4
甲 2
乙 10
SQL>
SQL> SELECT A, C, ROW_NUMBER() OVER(PARTITION BY MARK ORDER BY C DESC)
2 FROM (SELECT A, SUM(B) C, 'a' MARK FROM QZW GROUP BY A ORDER BY C DESC)
3 ;
A C ROW_NUMBER()OVER(PARTITIONBYMA
-------- ---------- ------------------------------
乙 13 1
甲 7 2
丙 4 3
SQL> drop table qzw;
Table dropped
说的有道里,那最好用dense_rank()SQL> select a,sum(b),dense_rank() over(order by sum(b) desc) rn
2 from test_06
3 group by a;A SUM(B) RN
---------- ---------- ----------
乙 13 1
甲 7 2
丁 4 3
丙 4 3
戊 2 4
insert into test_1(col1,col2) values('甲',5);
insert into test_1(col1,col2) values('乙',3);
insert into test_1(col1,col2) values('丙',4);
insert into test_1(col1,col2) values('甲',2);
insert into test_1(col1,col2) values('乙',10);select * from test_1;select col1,sum(col2) as col2,rank() over(order by sum(col2) desc) rn
from test_1
group by col1;
select A,sum(B)
GROUP BY A
而group 有隐含排序显示