要求是根据排名分配奖金,第一名100,第二名60,第三名30,,之前我写的是
select t.*,decode(t.ord,1,100,2,60,3,30) team_bounds from t
现在发现有名次有并列的情况,所以该语句不合适了,请教下,应该怎么改?表结构如下
ord set team d_time prize team_bounds
1 3 4 2011-09-01 33243.26955 100
2 3 5 2011-09-01 20353.63146 60
3 3 1 2011-09-01 13405.21866 30
4 3 3 2011-09-01 12246.7878
5 3 2 2011-09-01 9969.20349
1 4 4 2011-09-01 24731.75244 100
2 4 3 2011-09-01 23525.29311 60
3 4 5 2011-09-01 23246.72979 30
4 4 2 2011-09-01 18776.06016
select t.*,decode(t.ord,1,100,2,60,3,30) team_bounds from t
现在发现有名次有并列的情况,所以该语句不合适了,请教下,应该怎么改?表结构如下
ord set team d_time prize team_bounds
1 3 4 2011-09-01 33243.26955 100
2 3 5 2011-09-01 20353.63146 60
3 3 1 2011-09-01 13405.21866 30
4 3 3 2011-09-01 12246.7878
5 3 2 2011-09-01 9969.20349
1 4 4 2011-09-01 24731.75244 100
2 4 3 2011-09-01 23525.29311 60
3 4 5 2011-09-01 23246.72979 30
4 4 2 2011-09-01 18776.06016
from(--取系统时间前一天的所有机组对应所有班组的得分并按机组对total_prize排序
select rank() over(partition by t.set_code,t.day_time order by t.TOTAL_PRIZE desc) ord,t.set_code,t.team_code,t.day_time,t.TOTAL_PRIZE
from(--取系统时间前一天的所有机组对应所有班组的得分
select t.SET_CODE,t.TEAM_CODE,t.day_time,sum(t.TOTAL_PRIZE) as TOTAL_PRIZE
from v_jx_worktime_seg_p_list t where t.day_time is not null and t.day_time=to_char(sysdate-1, 'YYYY-MM-DD')
group by t.SET_CODE,t.TEAM_CODE,t.day_time)t)t
select t.*,decode(t.ord,1,decode(t.count_ord,1,100,2,80,3,63),2,decode(t.count_ord,1,60,2,45),3,30)team_bounds
from(--取系统时间前一天的所有机组对应所有班组的得分并按机组对total_prize排序
select count(*) over(partition by p.ord,p.set_code)count_ord,p.* from(
select dense_rank() over(partition by t.set_code,t.day_time order by t.TOTAL_PRIZE desc) ord,t.set_code,t.team_code,t.day_time ,t.TOTAL_PRIZE
from(--取系统时间前一天的所有机组对应所有班组的得分
select t.SET_CODE,t.TEAM_CODE,t.day_time,sum(t.TOTAL_PRIZE) as TOTAL_PRIZE
from v_jx_worktime_seg_p_list t where t.day_time is not null and t.day_time=to_char(sysdate-1, 'YYYY-MM-DD')
group by t.SET_CODE,t.TEAM_CODE,t.day_time)t)p)t