oracle 特殊SQL(TABLE( CAST( MULTISET()[zt]

arthurtangel發表於2012-11-01
OracleSQLAST

求條sql語句

成績表
--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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11990065/viewspace-748074/，如需轉載，請註明出處，否則將追究法律責任。

相關文章