奖学金分配问题

qingyun

大致意思：
   10个学生，前3名1等奖； 中间4名2等奖； 最后5名3等奖；


成绩表
--xh 学号  cj 成绩
create table b_cj(xh varchar2(50),cj number);
insert into b_cj values('1',67);
insert into b_cj values('2',63);
insert into b_cj values('3',77);
insert into b_cj values('4',68);
insert into b_cj values('5',97);
insert into b_cj values('6',62);
insert into b_cj values('7',77);
insert into b_cj values('8',79);
insert into b_cj values('9',73);
insert into b_cj values('10',65);

排名名额表
--mc 名次   rs 人数  jj 奖金
create table b_pmme(mc varchar(10),rs int,jj number)
insert into b_pmme values('1',3,'100');
insert into b_pmme values('2',4,'50');
insert into b_pmme values('3',5,'10');
想要的结果
根据成绩表的成绩降序排列，按排名名额表的名额确定排名和奖金。排名时不考虑相同成绩。
如下结果：
学号      成绩     名次   奖金
5        97          1        100
8        79          1        100
3        77          1        100
7        77          2         50
9        73          2         50
4        68          2         50
1        67          2         50
10        65          3         10
2        63          3         10
6        62          3         10



解决方法：

SELECT c.xh,c.cj,b.mc,b.jj
  FROM (SELECT c.*,ROW_NUMBER() OVER(ORDER BY cj DESC) rn FROM b_cj c) c
      ,(SELECT b.mc,b.jj,ROW_NUMBER() OVER(ORDER BY b.mc) rn
         FROM b_pmme b
            , TABLE( CAST( MULTISET( SELECT NULL
                                      FROM DUAL
                                   CONNECT BY LEVEL <= b.rs
                                   )
                            AS SYS.ODCIVARCHAR2LIST )  
                           )
       ) b
WHERE c.rn=b.rn;


能否不用MULTISET 这种方法，多弄些其他写法？

solomon_007

用半连接也可以：

SQL> select t.xh, t.cj, c.mc, c.jj
  2    from (select xh, cj, row_number() over(order by cj desc) rn from b_cj) t,
  3         (select a.mc,
  4                 a.rs,
  5                 a.jj,
  6                 b.n,
  7                 row_number() over(order by a.mc, b.n) rn2
  8            from b_pmme a,
  9                 (select level n
10                    from dual
11                  connect by level <= (select max(rs) from b_pmme)) b
12           where b.n <= a.rs) c
13   where t.rn = c.rn2
14  /
XH                                                         CJ MC                 JJ
-------------------------------------------------- ---------- ---------- ----------
5                                                          97 1                 100
8                                                          79 1                 100
3                                                          77 1                 100
7                                                          77 2                  50
9                                                          73 2                  50
4                                                          68 2                  50
1                                                          67 2                  50
10                                                         65 3                  10
2                                                          63 3                  10
6                                                          62 3                  10
10 rows selected

却早已分离

SQL> WITH t AS
  2   (SELECT xh, cj, row_number() over(ORDER BY cj DESC, xh) rn FROM b_cj),
  3  t1 AS
  4   (SELECT mc,
  5           nvl(SUM(rs) over(ORDER BY mc rows BETWEEN unbounded preceding AND 1
  6                    preceding),
  7               0) + 1 sta,
  8           jj,
  9           SUM(rs) over(ORDER BY mc) en
10      FROM b_pmme)
11  SELECT t.xh, t.cj, t1.mc, t1.jj
12    FROM t
13    JOIN t1
14      ON t.rn <= t1.en
15     AND t.rn >= t1.sta
16   ORDER BY 3, 2 DESC;

XH                 CJ MC                 JJ
---------- ---------- ---------- ----------
5                  97 1                 100
8                  79 1                 100
3                  77 1                 100
7                  77 2                  50
9                  73 2                  50
4                  68 2                  50
1                  67 2                  50
10                 65 3                  10
2                  63 3                  10
6                  62 3                  10

chengccy2010

1. SELECT XH, CJ, MC, JJ
   
2. FROM   (SELECT XH, CJ, ROW_NUMBER() OVER(ORDER BY CJ DESC) RN
   
3.          FROM   B_CJ) T
   
4. LEFT   JOIN (SELECT MC, RS, JJ, SUM(RS) OVER(ORDER BY MC) RS1
   
5.              FROM   B_PMME) T1
   
6. ON     T1.RS1 - T1.RS < T.RN
   
7. AND    T1.RS1 >= T.RN

复制代码

newkid

我就想问一句，为什么 10=4+3+5 ?

wangqing1026

with CC as (
select a.*,row_number()over(order by a.mc) xl
from b_pmme A，（select  rownum jb  from dual connect by rownum<=(select max(b.rs） from b_pmme b )) c
where c.jb<=a.rs ),
   DD as (select A.*,row_number()over(order by a.cj desc) xl from b_cj  A)
select DD.XH,DD.CJ,CC.MC,CC.JJ
from DD,CC
WHERE cc.xl=dd.xl

DICKY_XIE

学习了，我这个小菜鸟