查看: 14664|回復: 6

[SQL] 獎學金分配問題

[復制鏈接]
認證徽章
論壇徽章:
26
ITPUB新首頁上線紀念徽章
日期:2007-10-20 08:38:44ITPUB十周年紀念徽章
日期:2011-11-01 16:20:282012新春紀念徽章
日期:2012-01-04 11:49:542013年新春福章
日期:2013-02-25 14:51:24夏利
日期:2013-08-13 23:25:29優秀寫手
日期:2013-12-18 09:29:092014年新春福章
日期:2014-02-18 16:41:11馬上有車
日期:2014-02-18 16:41:11藍色妖姬
日期:2015-03-19 09:37:00ITPUB年度最佳技術原創精華獎
日期:2015-03-19 09:43:24
跳轉到指定樓層
1#
發表于 2018-1-12 09:18 | 只看該作者 回帖獎勵 |倒序瀏覽 |閱讀模式
大致意思:
   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 這種方法,多弄些其他寫法?

論壇徽章:
535
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:馬
日期:2008-10-08 17:01:01SQL大賽參與紀念
日期:2011-04-13 12:08:17授權會員
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉絲徽章
日期:2011-07-01 09:45:27ITPUB十周年紀念徽章
日期:2011-09-27 16:30:472012新春紀念徽章
日期:2012-01-04 11:51:22海藍寶石
日期:2012-02-20 19:24:27鐵扇公主
日期:2012-02-21 15:03:13
2#
發表于 2018-1-12 10:48 | 只看該作者
用半連接也可以:

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

使用道具 舉報

回復
求職 : 數據庫開發
論壇徽章:
24
秀才
日期:2017-08-11 15:38:46秀才
日期:2018-01-02 15:17:54秀才
日期:2018-01-02 15:18:22秀才
日期:2018-01-02 15:18:22秀才
日期:2018-01-02 15:18:22秀才
日期:2018-01-02 15:18:22秀才
日期:2018-01-02 15:18:22技術圖書徽章
日期:2018-01-02 15:18:30秀才
日期:2018-03-01 10:05:18秀才
日期:2018-05-22 15:21:20
3#
發表于 2018-1-12 13:09 | 只看該作者

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

使用道具 舉報

回復
論壇徽章:
17
優秀寫手
日期:2014-02-27 06:00:13秀才
日期:2017-08-11 15:37:32弗蘭奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48烏索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年紀念
日期:2017-05-02 15:22:36妮可·羅賓
日期:2017-04-06 10:06:19處女座
日期:2016-03-10 09:03:26白羊座
日期:2015-10-10 15:01:39
4#
發表于 2018-1-12 16:53 | 只看該作者
  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
復制代碼

使用道具 舉報

回復
論壇徽章:
527
奧運會紀念徽章:壘球
日期:2008-09-15 01:28:12生肖徽章2007版:雞
日期:2008-11-17 23:40:58生肖徽章2007版:馬
日期:2008-11-18 05:09:48數據庫板塊每日發貼之星
日期:2008-11-29 01:01:02數據庫板塊每日發貼之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春紀念徽章
日期:2009-01-04 14:52:28數據庫板塊每日發貼之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食紀念
日期:2009-07-22 09:30:00
5#
發表于 2018-1-12 23:45 | 只看該作者
我就想問一句,為什么 10=4+3+5 ?

使用道具 舉報

回復
論壇徽章:
0
6#
發表于 2018-1-13 15:02 | 只看該作者
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

使用道具 舉報

回復
認證徽章
論壇徽章:
0
7#
發表于 2018-1-16 14:15 | 只看該作者
學習了,我這個小菜鳥

使用道具 舉報

回復

您需要登錄后才可以回帖 登錄 | 注冊

本版積分規則 發表回復

DTCC2020中國數據庫技術大會 限時8.5折

【架構革新 高效可控】2020年9月21日~23日第十一屆中國數據庫技術大會將在北京隆重召開。

大會設置2大主會場,20+技術專場,將邀請超百位行業專家,重點圍繞數據架構、AI與大數據、傳統企業數據庫實踐和國產開源數據庫等內容展開分享和探討,為廣大數據領域從業人士提供一場年度盛會和交流平臺。

http://dtcc.it168.com


大會官網>>
30岁的男人干啥赚钱快赚钱多 贵州快三彩票网 辽宁11选5技巧 快乐8稳赚计划 华东i5选5带坐标走势图 在家赚钱网络兼职 中双色球一等奖的偏方 股票怎么开户 韩国快乐8彩官网 36选7一等奖中奖概率 网络上赚钱的方法
TOP技術積分榜 社區積分榜 徽章 團隊 統計 知識索引樹 積分競拍 文本模式 幫助
  ITPUB首頁 | ITPUB論壇 | 數據庫技術 | 企業信息化 | 開發技術 | 微軟技術 | 軟件工程與項目管理 | IBM技術園地 | 行業縱向討論 | IT招聘 | IT文檔
  ChinaUnix | ChinaUnix博客 | ChinaUnix論壇
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓優訊信息技術有限公司版權所有 聯系我們 
京ICP備09055130號-4  北京市公安局海淀分局網監中心備案編號:11010802021510 廣播電視節目制作經營許可證:編號(京)字第1149號
  
快速回復 返回頂部 返回列表
贵州快三彩票网 辽宁11选5技巧 快乐8稳赚计划 华东i5选5带坐标走势图 在家赚钱网络兼职 中双色球一等奖的偏方 股票怎么开户 韩国快乐8彩官网 36选7一等奖中奖概率 网络上赚钱的方法