查询计算每个地区亏损前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 : [1] [2] 下一页 没有相关教程
|