查询计算每个地区亏损前10名的语句的写法
代码:--------------------------------------------------------------------------------
下面2个语句均可以达到计算亏损前10名的要求
SQL>select * from (select rank() over(order by b04_50) 名次, b04_50 "亏损总额" from cj604 where b04_50<-1000 ) where rownum<=10;
名次 亏损总额
---------- ----------
1
2
3
4
5
6
7
8
9
10
SQL> select rownum 名次,a.* from (select b04_50 "亏损总额" from cj604 where b04_50<-1000 order by b04_50 ) a where rownum<=10;
名次 亏损总额
---------- ----------
1
2
3
4
5
6
7
8
9
10
如何才能实现计算每个地区亏损前10名,按地区码排序,问题是一个地区的前10可能比另一个地区前10大或小或在中间，如下面71/72就是混在一起的
SQL> select rownum 名次,a.* from (select substr(z01_04,1,2) 地区码,b04_50 "亏损总额" from cj604 where b04_50<0 order by b04_50 ) a where rownum<=10;
名次 地区 亏损总额
---------- ---- ----------
1 71
2 72
3 72
4 71
5 71
6 71
7 72
8 71
9 72
10 71
自己试验出来了
代码:--------------------------------------------------------------------------------
select * from (select substr(z01_04,1,2) 地区码,DENSE_RANK() OVER (PARTITION BY substr(z01_04,1,2) order by b04_50) 名次, b04_50 "亏损总额" from jb604,j601 where b04_50<0 and j601.uuid=jb604.uuid ) where 名次<=10;
------------------------------------------------------------------------------------
这里不想多说其他的, 不过希望LZ自己注意你是该用 dense_rank()还是rank()还是row_number()
前十名? 是否有并列的? 如果出现并列怎么处理?
代码:--------------------------------------------------------------------------------
SQL> select id, num,
2 dense_rank() over(partition by id order by num desc) as dr,
3 rank() over(partition by id order by num desc) as r,
4 row_number() over(partition by id order by num desc) as rn
5 from t_rank
6 /
ID NUM DR R RN
---- ----- ---------- ---------- ----------
1 14 1 1 1
1 14 1 1 2
1 13 2 3 3
1 13 2 3 4
1 12 3 5 5
1 11 4 6 6
1 11 4 6 7
1 11 4 6 8
1 10 5 9 9
1 10 5 9 10
2 10 1 1 1
2 9 2 2 2
2 8 3 3 3
2 7 4 4 4
2 6 5 5 5
2 5 6 6 6
2 4 7 7 7
2 3 8 8 8
2 2 9 9 9
2 1 10 10 10
20 rows selected
--------------------------------------------------------------------------------------
如果上面的数据, 我要选择不同ID的前3名, 大家觉得要怎么处理呢?
用where dr<=3? where r<=3? 还是 where rn<=3?
__________________
平凡的伟大, 伟大的平凡
msn : jackywood_24@hotmail.com
