• <sup id="my82s"><source id="my82s"></source></sup>
  • <legend id="my82s"><div id="my82s"></div></legend>
    <li id="my82s"><tt id="my82s"></tt></li>
    <bdo id="my82s"><rt id="my82s"></rt></bdo>
  • 查看: 26366|回复: 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 这种方法,多弄些其他写法?

    论坛徽章:
    548
    生肖徽章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:222012新春纪念徽章
日期:2020-11-30 22:13:24海蓝宝石
日期:2012-02-20 19:24:27
    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

    使用道具 举报

    回复
    论坛徽章:
    18
    优秀写手
日期:2014-02-27 06:00:13秀才
日期:2017-12-12 09:52:44秀才
日期: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
    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
    复制代码

    使用道具 举报

    回复
    论坛徽章:
    519
    奥运会纪念徽章:垒球
日期: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 | 只看该作者
    学习了,我这个小菜鸟

    使用道具 举报

    回复

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

    本版积分规则 发表回复

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