查看: 6335|回复: 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
发表于 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 这种方法,多弄些其他写法?

论坛徽章:
326
生肖徽章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
发表于 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
发表于 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
发表于 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
复制代码

使用道具 举报

回复
论坛徽章:
526
奥运会纪念徽章:垒球
日期: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
发表于 2018-1-12 23:45 | 显示全部楼层
我就想问一句,为什么 10=4+3+5 ?

使用道具 举报

回复
论坛徽章:
0
发表于 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
发表于 2018-1-16 14:15 | 显示全部楼层
学习了,我这个小菜鸟

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

第67期:Neo4j图数据库平台架构最佳实践
【微学堂】10月18日 20:00(周四)

当下,数据的规模和类型每时每刻都在呈几何级数的增长,仅能够管理大量的数据是不够的,关键是能从海量数据中发掘出有用的信息,特别是数据之间的关联,能高效存储和处理数据之间关联的新型数据库为图数据库。 本讲座将介绍Neo4j图数据库的基本概念、设计特点、架构和经典应用场景实战分享。

进入课堂>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表