排名sql
我有一个table t，u
t里面字段Group,SCORE（每个组总分数）
u字段Group，Num（每个组的人数）

group SCORE
A 30
B 20


Group Num
A 10
B 11

我现在的算法是，如果一个组的平均分是第一改组得20元，第二得元，如果并列，则平分20元，（只有两个组A B）

这种SQL怎么写

------解决方案--------------------
不一定要用一条SQL完成吧，用PL/SQL语句块判断实现。
------解决方案--------------------
select bb.GROUPNAME,
bb.AVG_SCORE,
bb.mingci,
case
when bb.mingci = 1 then
20
else
0
end jiangli from（SELECT AA.GROUPNAME,
AA.AVG_SCORE,
dense_rank() over(order bY AA.AVG_SCORE desc) mingci
FROM (SELECT T.GROUPNAME, T.SCORE / U.NUM AVG_SCORE
FROM T, U
WHERE T.GROUPNAME = U.GROUPNAME) AA) bb;
------解决方案--------------------
select group,sn,rn,ct,decode(rn,1,20/ct
2,10/ct) sal from 
(
(select group, score/num sn ,dense_rank()over(order by score/num) rn from t,u 
where t.group=u.group) a,
(select rn,count(*) ct from (
select group, score/num sn,dense_rank()over(order by score/num) rn from t,u where t.group=u.group)
group by rn) b
)
where a.rn=b.rn



没测试，稍后给出测试数据
------解决方案--------------------

SQL code

create or replace  table t(group,score);
create or replace table  u(group,num);
insert into t values ('A',500);
insert into t values ('B',500);
insert into t values ('C',300);
insert into u values ('A',20);
insert into u values ('B',20);
insert into u values ('C',8);

select a.group,sn,a.rn,b.ct,decode(a.rn,1,20/ct
                                        2,10/ct) sal 
    from
     (select group, score/num sn,dense_rank()over(order by score/num desc) rn  from t,u  where t.group=u.group) a,
     (select rn,count(*) ct from 
     (select group, score/num sn,dense_rank()over(order by score/num desc) rn from t,u where t.group=u.group) group by rn) b   
where a.rn=b.rn


group     sn   rn    ct    sal
------  -----  ----  ----  ---
C         37.5  1      1     20
B         25    2      2      5
A         25    2      2      5