排名sql,该怎么处理

排名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.rngroup     sn   rn    ct    sal------  -----  ----  ----  ---C         37.5  1      1     20B         25    2      2      5A         25    2      2      5