查看: 10640|回复: 49

[精华] “盛拓传媒杯”SQL数据库编程大赛第四期评分及所有参赛选手答题公布!

[复制链接]
招聘 : 数据库管理员
认证徽章
论坛徽章:
83
IT宝贝
日期:2013-11-15 18:40:242015年新春福章
日期:2015-03-06 11:57:31美羊羊
日期:2015-03-04 14:48:58马上加薪
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11
发表于 2011-4-12 17:18 | 显示全部楼层 |阅读模式
经过多日进展的评审,第四期评审结果现在揭晓!具体评审得分及点评如下,附件中依旧有所有答题的压缩包和点评表格!

TOP10得主正在统计中,稍后就公布,大家别着急!参与的puber等我的pm,我一会就发给大家!

评审代号综合得分点评及建议
SQL4-3399.15把一行消费记录拆分为增(+1)、减(-1)两行并对标记累加得到人数,比其他的拆分方法减少了中间结果从而提高了效率 。T4排重思路有些费解,newcnt如何经过两次累加演变成人数curr_cnt,最好多加注释。
SQL4-2695.65代码思路清晰,高效。本解法把时间区间划分为较大颗粒(见集合date_piece),比起拆分到天的做法会高效一些.
SQL4-495题目要求的total_fee写成了fee_total,略微扣点点分,用GROUP BY ROLLUP做横向合计比较新鲜。
SQL4-2094代码清晰高效,注释不足,row_number()去除重复不太必要,直接在算headcount的时候用COUNT(DISTINCT USER_ID)即可。
SQL4-2592.6代码思路清晰。不过t和m的连接是多余的,直接用t和service_usage连接即可。最后和companies和services的外连接可改为内连接,因为题目已说明有外键存在。
SQL4-1292.15直接了当的拆分到天的做法。
SQL4-2792.1结构清晰,该题对mssql而言确实构造会繁琐一些
SQL4-891.2本解法把时间区间划分为较大颗粒,比起拆分到天的做法会高效一些。tmp_date2里面的自连接可以改为分析函数实现,如果再精心改写的话UNION也可以去掉。
SQL4-291代码书写非常规范,但效率不高,中间CTE构造略显啰嗦,use_days和category可以合并为一层。use_fee_all里面的TO_CHAR和NVL有些多余。
SQL4-3990.75process_data里面虽然是拆分到天,但用的是两个数字而不是一个日期型来表示,有些独特。但此答题的性能较差。
SQL4-1589.5结构清晰,但构造cte过程略显啰嗦。t3里面的DISTINCT 子查询可以不要,直接COUNT(DISTINCT user_id). t4里面的GROUP BY没有必要带t3.service_date。用户数在service_rates的定位应该写到WHERE里面去。
SQL4-3488.4该代码利用了pivot新特性,但效率较差。t1中把services s,companies c连接进去有些太早,这样增加了分组键company_name和service_name,从而增加了负担。
SQL4-1088.25tmp1的过滤有误,a的起止日期可能全部落在输入区间之外,但是包含输入期间。
SQL4-1987.3本解法把时间区间划分为较大颗粒(见集合C),比起拆分到天的做法会高效一些 。构造集合d1和d的思路比较难懂,注释不是很充分。
SQL4-2885代码性能稍差,usage_match和period用内连接即可。
SQL4-383.9中间CTE过程稍显啰嗦,usage_usernumber_by_day没有去除重复。几个中间结果的排序是不必要的。
SQL4-1682.1代码较高效,但cte嵌入代码行中略为难读。step2没有去除重复。step3的DECODE没有必要。
SQL4-1381.8service_usage3里的日期过滤可以提早到service_usage1完成,才不会全表做拆分。全文没有一句注释,且结果未排序。
SQL4-3581.75直接了当的拆分到天的做法。但性能较差。companyB的serviceA的FEE1计算有误,导致总数有错误。
SQL4-2180.9ALL_TIME里最后两个UNION没有必要,在前两个UNION的时候界定一下就可以了。没有去除重复。但结果列标题不符合题目要求。
SQL4-4179.6day_cnt_stat里面的日期过滤条件写错了,应该是AND不是OR. 没有去除重复。
SQL4-2277.7子查询usages的CASE没有必要那么复杂,应该在WHERE做区间的过滤。构造出来的NULL也给后面增加了负担。
SQL4-1876.2没有去除重复。sc里面的排序是不必要的。
SQL4-1775.6没有去除重复;对日期的过滤可以推入到service_usage表,这样就不需要全表都做多行拆分。用TABLE()函数拆分比较新颖。
SQL4-3675.45条件判断过于复杂,且缺乏注释,g_comp_sev_to_day的CASE可以简单地用GREATEST,LEAST完成。没有去除重复。
SQL4-2975.25结构清晰,性能稍差。计费的时候应该使用distinct_user_cnt而不是user_servcie_cnt,这个错误导致重复计费。
SQL4-3774.65tsrv的过滤有误,性能较差。t的起止日期可能全部落在输入区间之外,但是包含输入期间。tcnt没有去重复。
SQL4-4072.2没有去除重复。最后的外连接有些不必要,题目没有要求输出所有公司和服务的组合。
SQL4-3870.85性能较差。a里面应该根据输入区间做过滤,减少后面的计算负担。d里面没有去除重复。
SQL4-566.9未排序;t3的cnt去除了重复,t3和t4连接的时候,也能够找到正确的费率段,但是同一个人的交叉情况会重复计费sum。
SQL4-139.501集合tds和service_usage的连接条件有误;tmp_date_scale的构造思路也有误,start_date和end_date是首尾包含的,即使它们相等也应该算使用一天。同一用户使用期间的交叉情况也没有考虑。
SQL4-728.3拆分记录时,区间分为 60天以上和以下,不知道是什么道理。最后行转列的计费部分出了错,忘记乘以人数了,SUM(RATE)应该为SUM(RATE*cn)
SQL4-1412.1DB2的,非参赛指定语言,相关地方改为Oracle语法后,得出错误结果
SQL4-911.2计费公式有误,既然使用记录已经拆分到天,应该逐一算每天的费用然后汇总:SUM(DECODE(CATEGORY_ID,1,cnt*rate)) 而不是分开汇总再来相乘。
SQL4-2311结果完全错误。V1的num_day计算有误,没有去除重复而且分区键不够充分,会把不同company_id的用户混起来。日期过滤条件应该是>=和<=。
SQL4-1110.7x的过滤条件有误,会遗漏很多数据。all_user的计算错误,要求是同一天出现的用户数。
SQL4-610service_company_rate做行转列有些太早了。service_users_usage的GROUP BY方法有误,会导致交叉的时段分到不同组,从而无法正确计算某天的人数。
SQL4-2410结果完全错误。没用指定的绑定变量做输入;没有去除重复。fee1~fee4的计费公式弄错了,对题意理解有误。
SQL4-3010子查询U中对用户数的计算是错误的,按题意应该是每天使用服务的人数。这个理解上的失误导致整个结果出错。
SQL4-3110子查询T中对用户数的计算是错误的,按题意应该是每天使用服务的人数。这个理解上的失误导致整个结果出错。
SQL4-3210将开始、结束分为两种事件并赋予标记的思路是可取的, 但是思路过于繁复导致出错。比如event_type的累加可以简单地用分析函数SUM实现而无需用递归。


附上此次SQL大赛活动相关链接,方便大家查阅:
第四期SQL大赛活动链接:http://www.itpub.net/thread-1411495-1-1.html
第三期SQL大赛活动链接:http://www.itpub.net/thread-1408182-1-1.html
第二期SQL大赛活动链接:http://www.itpub.net/thread-1403356-1-1.html
第一期SQL大赛活动链接:http://www.itpub.net/thread-1400067-1-1.html

SQL数据库编程大赛第三期评分及所有答题公布链接:http://www.itpub.net/thread-1415335-1-1.html
SQL数据库编程大赛第二期评分及所有答题公布链接:http://www.itpub.net/thread-1411980-1-1.html
SQL数据库编程大赛第一期评分及所有答题公布链接:http://www.itpub.net/thread-1407072-1-1.html

盛拓传媒杯SQL大赛第四期答题汇总.rar

58.73 KB, 下载次数: 166

sql大赛第四期评分结果及评语表.rar

5.09 KB, 下载次数: 89

招聘 : 数据库管理员
认证徽章
论坛徽章:
83
IT宝贝
日期:2013-11-15 18:40:242015年新春福章
日期:2015-03-06 11:57:31美羊羊
日期:2015-03-04 14:48:58马上加薪
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11
发表于 2011-4-12 17:21 | 显示全部楼层
此题newkid解题思路总结公布如下,另此贴还附上此解题思路的文档下载,方便大家查阅:

这道题的风格比较务实,不像前三题带有游戏的性质,算是回归了SQL的传统功能。尽管它煞有介事地用了五张表,对经验丰富的程序员来说仍然是小菜一碟,不少人觉得难度降低了。尽管如此,仍然有将近一半的人没有给出正确答案,可见简单的题目也不可轻视,此外,如何把一道看似平庸的题目做出新意,这才是难点所在。

在这里要特别感谢regonly1同学,他写了一个脚本制造测试数据,本来我是要自己动手的,现在就顺手牵羊,以逸待劳了。实际上我自己的答案也是利用他产生的数据来调试、改良的。

题目的计费公式和人数相关,因此我们首先要算出计费区间内的人数,然后再去套用计费公式。每笔使用记录的区间都各不相同,长短不一,给分组统计造成了障碍,因此不难想到把这些记录分割成小单元让它们对齐。最直接的思路就是分割到最小计费单位即日期,把一行使用记录按其覆盖的日期拆分成多行,每天一行,然后按日期汇总人数,再用这个人数去计费。这是很多人采用的办法,示例如下:

  1. SELECT t.company_id
  2.       ,c.company_name
  3.       ,t.service_id
  4.       ,s.service_name
  5.       ,t.fee1
  6.       ,t.fee2
  7.       ,t.fee3
  8.       ,t.fee4
  9.       ,t.fee1+t.fee2+t.fee3+t.fee4 as total_fee
  10.   FROM (SELECT u.company_id
  11.               ,u.service_id
  12.               ----- 行转列输出
  13.               ,NVL(SUM(DECODE(r.category_id,1,r.rate*u.user_cnt*u.days)),0) AS fee1
  14.               ,NVL(SUM(DECODE(r.category_id,2,r.rate*u.user_cnt*u.days)),0) AS fee2
  15.               ,NVL(SUM(DECODE(r.category_id,3,r.rate*u.user_cnt*u.days)),0) AS fee3
  16.               ,NVL(SUM(DECODE(r.category_id,4,r.rate*u.user_cnt*u.days)),0) AS fee4
  17.          FROM (SELECT ----- 把同一公司、同一服务、人数一致的记录做汇总,算出总天数
  18.                       ----- 这一层聚合运算是为了减少中间结果集的行数,减轻后面行转列的负担
  19.                       company_id
  20.                      ,service_id         
  21.                      ,user_cnt
  22.                      ,COUNT(*) days ---- 这样的人数总共有几天
  23.                 FROM (SELECT usr.company_id     
  24.                             ,usg.service_id
  25.                             ,COUNT(DISTINCT usg.user_id) user_cnt --------- 每个用户在每天只按1人算,DISTNCT 去除重复
  26.                         FROM service_usage usg
  27.                             ,service_users usr
  28.                             ,(SELECT ROWNUM rn  ---- 根据输入的起止日期构造出以个N行集合,连接后就把一行拆成多行。
  29.                                 FROM DUAL
  30.                               CONNECT BY ROWNUM<=TO_DATE(:p_end_date,'YYYYMMDD')-TO_DATE(:p_start_date,'YYYYMMDD')+1
  31.                               )
  32.                       WHERE usg.user_id = usr.user_id
  33.                             AND usg.end_date>=TO_DATE(:p_start_date,'YYYYMMDD') ---- 使用记录必需落在输入的起止日期的区间内
  34.                             AND usg.start_date<=TO_DATE(:p_end_date,'YYYYMMDD')
  35.                             ---下面的这个条件限制了当前使用记录要拆成多少天,LEAST和GREATEST是排除输入区间外的日期
  36.                             AND rn <= LEAST(usg.end_date,TO_DATE(:p_end_date,'YYYYMMDD'))+1
  37.                                       -GREATEST(usg.start_date,TO_DATE(:p_start_date,'YYYYMMDD'))
  38.                       GROUP BY usr.company_id     
  39.                               ,usg.service_id
  40.                               ,GREATEST(usg.start_date,TO_DATE(:p_start_date,'YYYYMMDD'))+rn-1 ---- 拆出来的每天的日期
  41.                       )
  42.                GROUP BY company_id,service_id,user_cnt
  43.                ) u
  44.                ,service_rates r
  45.                WHERE u.service_id = r.service_id
  46.                      AND u.company_id = r.company_id
  47.                      AND u.user_cnt BETWEEN r.user_count_min AND r.user_count_max   ---- 属于哪一档收费标准
  48.         GROUP BY u.company_id
  49.               ,u.service_id
  50.         ) t
  51.        ,companies c
  52.        ,services s
  53. WHERE t.company_id=c.company_id AND t.service_id=s.service_id
  54. ORDER BY company_id,service_id;
复制代码


上述方法有个美中不足之处,就是在把一行数据拆成多行时,可能产生很多中间记录,特别是当使用记录的覆盖范围比较大时,一行要分成很多行,效率不如人意。实际上数据没有必要打得那么“碎”,如果我们能够求出所有的交叠的区间,那么只要用该区间的首尾日期相减就可以得到这一段的天数了,而不必拆分到每一天再加回来。例如有四个从小到大的日期A,B,C,D, 某一行使用记录区间是AC, 另一条是BD,它们之间的交叠部分是BC这一段。我们只要把第一条拆成AB和BC两段,而第二条只需拆成BC和CD两段,那么AB这段是一条,BC这段有两条,CD这段仍然是一条,这样就分组计数就可以很容易求出来。从这个例子我们也可以看到分段的思路,就是把每条使用记录的首尾日期拿出来进行排序,这样排序后的两行数据之间自然而然就形成了一个个区间。

  1. WITH date_periods AS (
  2. ---- 这个子查询求出所有交叠部分的区间,拿它作为打散数据的刻度表
  3. SELECT t.*
  4.        ---- 两行数据之间的日期相减得到区间的大小。
  5.        ---- N个区间总共会有N+1条记录,最后一行的LEAD是NULL所以算出来的DAYS也会是NULL, 等会要过滤掉。
  6.       ,LEAD(period_date) OVER(PARTITION BY company_id,service_id ORDER BY period_date) - period_date AS days
  7.   FROM ( SELECT DISTINCT ----- 去除刻度表中的重复日期
  8.                 usr.company_id     
  9.                ,usg.service_id  
  10.                 ---- LEAST和GREATEST是排除输入区间外的日期
  11.                ,(CASE WHEN rn=1 THEN GREATEST(usg.start_date,TO_DATE(:p_start_date,'YYYYMMDD'))
  12.                       ---- 下面end_date+1是为了把闭合区间转换为前闭后开区间,这样只需相减就可得出天数
  13.                       ELSE LEAST(usg.end_date,TO_DATE(:p_end_date,'YYYYMMDD'))+1
  14.                  END) period_date
  15.             FROM service_usage usg
  16.                 ,service_users usr
  17.                 ,(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=2) --- 把一行拆成两行, rn=1起始日期,rn=2终止日期
  18.            WHERE usg.user_id = usr.user_id
  19.                  AND usg.end_date>=TO_DATE(:p_start_date,'YYYYMMDD')---- 使用记录必需落在输入的起止日期的区间内
  20.                  AND usg.start_date<=TO_DATE(:p_end_date,'YYYYMMDD')
  21.         ) t
  22. )
  23. SELECT --------- 下面开始的写法和上一种答案差不多
  24.        t.company_id
  25.       ,c.company_name
  26.       ,t.service_id
  27.       ,s.service_name
  28.       ,t.fee1
  29.       ,t.fee2
  30.       ,t.fee3
  31.       ,t.fee4
  32.       ,t.fee1+t.fee2+t.fee3+t.fee4 as total_fee
  33.   FROM (SELECT u.company_id
  34.               ,u.service_id
  35.               ,NVL(SUM(DECODE(r.category_id,1,r.rate*u.user_cnt*u.days)),0) AS fee1
  36.               ,NVL(SUM(DECODE(r.category_id,2,r.rate*u.user_cnt*u.days)),0) AS fee2
  37.               ,NVL(SUM(DECODE(r.category_id,3,r.rate*u.user_cnt*u.days)),0) AS fee3
  38.               ,NVL(SUM(DECODE(r.category_id,4,r.rate*u.user_cnt*u.days)),0) AS fee4
  39.          FROM (SELECT ----- 把同一公司、同一服务、人数一致的记录做汇总,算出总天数
  40.                       ----- 这一层聚合运算是为了减少中间结果集的行数,减轻后面行转列的负担
  41.                       company_id
  42.                      ,service_id         
  43.                      ,user_cnt
  44.                      ,SUM(days) days ---- 这样的人数总共有几天
  45.                 FROM (SELECT usr.company_id     
  46.                             ,usg.service_id  
  47.                             ,COUNT(DISTINCT usg.user_id) user_cnt
  48.                             ,d.days
  49.                          FROM service_usage usg
  50.                              ,service_users usr
  51.                              ,date_periods d ------ 用上面产生的刻度表连接回使用记录表,得到分段
  52.                         WHERE usg.user_id = usr.user_id
  53.                               AND usg.end_date>=TO_DATE(:p_start_date,'YYYYMMDD')
  54.                               AND usg.start_date<=TO_DATE(:p_end_date,'YYYYMMDD')
  55.                               AND usr.company_id = d.company_id
  56.                               AND usg.service_id = d.service_id  
  57.                               AND d.period_date BETWEEN usg.start_date AND usg.end_date
  58.                               AND d.days>0  ----- 过滤掉最后一行
  59.                        GROUP BY usr.company_id     
  60.                                ,usg.service_id  
  61.                                ,d.period_date
  62.                                ,d.days
  63.                       )
  64.                GROUP BY company_id,service_id,user_cnt
  65.                ) u
  66.                ,service_rates r
  67.                WHERE u.service_id = r.service_id
  68.                      AND u.company_id = r.company_id
  69.                      AND u.user_cnt BETWEEN r.user_count_min AND r.user_count_max   ---- 属于哪一档收费标准
  70.         GROUP BY u.company_id
  71.               ,u.service_id
  72.         ) t
  73.        ,companies c
  74.        ,services s
  75. WHERE t.company_id=c.company_id AND t.service_id=s.service_id
  76. ORDER BY company_id,service_id;
复制代码


更进一步,有没有办法不将记录拆散呢?我们沿用上述的求区间思路,对这些区间进行观察。这些时间区间的划分,已经充分考虑了交叉的情况,保证了这段区间内人数是固定不变的。人数的变化发生在区间的首尾。通过观察,我们可以看到这些区间的首尾无非就是用户增加(如果日期来自start_date)或减少(如果日期来自end_date),如果用户A的一个使用记录被刻度表割成N段,那么该用户对人数的影响仅在首尾两段存在。我们把人数增加标记为+1的记录,减少则标记为-1的记录,如果对这些增减标记沿着时间轴来作一个逐行累计(利用分析函数SUM(flag) OVER(...)),则本例子中那些中间那些“不受影响”的区间会“继承”前面的累计结果(所谓不受影响是指不受用户A的影响,因为用户A还处于区间内。人数肯定是要变的,这是由形成区间的其他用户记录决定的),这个SUM就是我们想要的区间的人数。这个方法只需把每行使用记录拆成+1和-1的两行,而无需把刻度表连接回使用记录表。

这个方法还存在一点障碍,就是同一用户自己使用记录的交叉情况。因为+1/-1标记的累加是很盲目的,我们无从得知到底有多少用户留在本区间内。这就要求在实施上述方法之前,必需对用户使用记录做一个去重合并,即如果有用户发生了使用记录交叉的情况,必需先合并为连续记录。我们抛开所有其他用户的记录,只对单个用户自己切割形成的区间进行观察。如果没有交叠情况,则这个标记的累计值将会是1(开始)和0(结束)。如果发生了交叠,则这个累计值会升高,最后又逐渐回到0。那么我们只要把首尾的累计值为1和0的记录找出来,中间的都可以丢弃,这样就得到了一个合并后的区间。如果仅按日期排序累加,那么假如有两条记录恰好是同一天开始,则这个累计值将从2开始而不是1。为了辨别这种情况,我们在排序的时候特地加入一个唯一ID(下面用了ROWNUM)作为排序键,保证一定有一条记录是从1开始,从0结束。

下面就是这个思路的具体实现,merged_usage子查询是合并交叉区间的过程。

  1. WITH merged_usage AS ( ------- 合并过的使用记录              
  2. SELECT *
  3. FROM (SELECT service_id,user_id,flag,period_date
  4.              ,SUM(flag) OVER(PARTITION BY service_id,user_id ORDER BY period_date,rn) sum_flag
  5.          FROM (SELECT usg.service_id
  6.                      ,usg.user_id  
  7.                       ---- LEAST和GREATEST是排除输入区间外的日期
  8.                      ,(CASE WHEN rn=1 THEN GREATEST(usg.start_date,TO_DATE(:p_start_date,'YYYYMMDD'))
  9.                             ---- 下面end_date+1是为了把闭合区间转换为前闭后开区间,这样只需相减就可得出天数
  10.                             ELSE LEAST(usg.end_date,TO_DATE(:p_end_date,'YYYYMMDD'))+1
  11.                        END) period_date --- 区间日期用于计算天数。end_date+1是为了转换为前闭后开区间,这样只需相减就可得出天数
  12.                      ,(CASE WHEN rn=1 THEN 1 ELSE -1 END) flag ---- 起始为+1, 终止为-1
  13.                      ,ROWNUM rn
  14.                  FROM service_usage  usg
  15.                      ,(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=2) --- 把一行拆成两行, rn=1起始,rn=2终止
  16.                  WHERE usg.end_date>=TO_DATE(:p_start_date,'YYYYMMDD')---- 使用记录必需落在输入的起止日期的区间内
  17.                        AND usg.start_date<=TO_DATE(:p_end_date,'YYYYMMDD')
  18.               ) u
  19.        )
  20. WHERE flag=1 AND sum_flag=1 OR flag=-1 AND sum_flag=0
  21. )
  22. ,u2 AS  (
  23.    ------这一层聚合运算并不是必需的,只是试图减少中间结果的行数,减轻下次计算的负担
  24.    SELECT ------ 按公司、服务和日期进行聚合,算出这一段区间的用户增减数
  25.           usr.company_id  
  26.          ,usg.service_id  
  27.          ,usg.period_date
  28.          ,SUM(usg.flag) AS net_flag_sum ---- 起始为+1, 终止为-1, 求和得出用户数增减数净值
  29.      FROM merged_usage  usg
  30.          ,service_users usr
  31.     WHERE usg.user_id = usr.user_id
  32.     GROUP BY usr.company_id
  33.             ,usg.service_id  
  34.             ,usg.period_date
  35. )
  36. ,u3 AS (
  37.    SELECT company_id
  38.          ,service_id
  39.           ----- 沿着时间轴截至当前的用户增减数逐行累计值,即该区间内的用户数
  40.          ,SUM(net_flag_sum) OVER(PARTITION BY company_id,service_id ORDER BY period_date) user_cnt
  41.           ---- 两行数据之间的日期相减得到区间的大小。
  42.          ,LEAD(period_date) OVER(PARTITION BY company_id,service_id ORDER BY period_date) - period_date as days
  43.      FROM u2
  44.   )
  45. ,u4 AS (
  46.    ------这一层聚合运算并不是必需的,只是试图减少中间结果的行数,减轻下次计算的负担
  47.    ------仅仅在user_cnt有很多重复的时候才有意义
  48.    SELECT company_id
  49.          ,service_id
  50.          ,user_cnt
  51.          ,SUM(days) AS days --- 这个人数的总天数
  52.      FROM u3
  53.    GROUP BY company_id
  54.            ,service_id
  55.            ,user_cnt
  56.   )
  57. SELECT t.company_id
  58.       ,c.company_name
  59.       ,t.service_id
  60.       ,s.service_name
  61.       ,t.fee1
  62.       ,t.fee2
  63.       ,t.fee3
  64.       ,t.fee4
  65.       ,t.fee1+t.fee2+t.fee3+t.fee4 as total_fee
  66.   FROM (SELECT company_id,service_id
  67.               ,NVL(MAX(DECODE(category_id,1,fee)),0) fee1
  68.               ,NVL(MAX(DECODE(category_id,2,fee)),0) fee2
  69.               ,NVL(MAX(DECODE(category_id,3,fee)),0) fee3
  70.               ,NVL(MAX(DECODE(category_id,4,fee)),0) fee4
  71.           FROM (SELECT u.company_id ------- 这一层聚合运算是为了减少行转列的DECODE工作量。
  72.                       ,u.service_id
  73.                       ,r.category_id
  74.                       ,SUM(r.rate*u.user_cnt*u.days) fee
  75.                   FROM u4 u
  76.                        ,service_rates r
  77.                        WHERE u.service_id = r.service_id
  78.                              AND u.company_id = r.company_id
  79.                              AND u.user_cnt BETWEEN r.user_count_min AND r.user_count_max   ---- 属于哪一档收费标准
  80.                 GROUP BY u.company_id
  81.                         ,u.service_id
  82.                         ,r.category_id
  83.                 )
  84.         GROUP BY company_id,service_id
  85.         ) t
  86.        ,companies c
  87.        ,services s
  88. WHERE t.company_id=c.company_id AND t.service_id=s.service_id
  89. ORDER BY company_id,service_id
  90. ;
复制代码

newkid第四期解题说明.pdf

84.79 KB, 下载次数: 77

使用道具 举报

回复
认证徽章
论坛徽章:
17
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51ITPUB14周年纪念章
日期:2015-10-26 17:23:44itpub13周年纪念徽章
日期:2014-10-08 15:17:53itpub13周年纪念徽章
日期:2014-10-08 15:17:53itpub13周年纪念徽章
日期:2014-10-08 15:17:53itpub13周年纪念徽章
日期:2014-10-08 15:17:532013年新春福章
日期:2013-04-08 17:42:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:03:32奥运会纪念徽章:皮划艇激流回旋
日期:2012-09-18 11:19:40奥运会纪念徽章:花样游泳
日期:2012-08-22 18:26:54
发表于 2011-4-12 17:35 | 显示全部楼层
坐沙发

使用道具 举报

回复
认证徽章
论坛徽章:
17
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51ITPUB14周年纪念章
日期:2015-10-26 17:23:44itpub13周年纪念徽章
日期:2014-10-08 15:17:53itpub13周年纪念徽章
日期:2014-10-08 15:17:53itpub13周年纪念徽章
日期:2014-10-08 15:17:53itpub13周年纪念徽章
日期:2014-10-08 15:17:532013年新春福章
日期:2013-04-08 17:42:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:03:32奥运会纪念徽章:皮划艇激流回旋
日期:2012-09-18 11:19:40奥运会纪念徽章:花样游泳
日期:2012-08-22 18:26:54
发表于 2011-4-12 17:39 | 显示全部楼层
经过大师指点发现不足了

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有房
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31
发表于 2011-4-12 17:57 | 显示全部楼层
sql大赛参赛和评审落下帷幕了,接下来就是发奖了……
提前恭喜获奖的各位——虽然我们还不知道都是谁

使用道具 举报

回复
论坛徽章:
32
祖国60周年纪念徽章
日期:2009-10-09 08:28:002013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2013-06-28 11:09:23ITPUB季度 技术新星
日期:2013-07-30 16:04:58优秀写手
日期:2013-12-18 09:29:132014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09红孩儿
日期:2014-03-04 16:40:38美羊羊
日期:2015-02-16 16:36:28懒羊羊
日期:2015-03-04 14:52:11
发表于 2011-4-12 18:03 | 显示全部楼层
我晕,题目开始时没说明要不要去重复吧?怎么后来都来个“没有去除重复”呢?

使用道具 举报

回复
论坛徽章:
19
授权会员
日期:2007-10-19 09:45:022013年新春福章
日期:2013-02-25 14:51:24奥运会纪念徽章:体操
日期:2012-10-09 16:13:522012新春纪念徽章
日期:2012-01-04 11:50:44SQL数据库编程大师
日期:2011-04-13 12:09:01SQL大赛参与纪念
日期:2011-04-13 12:08:172011新春纪念徽章
日期:2011-02-18 11:43:342009日食纪念
日期:2009-07-22 09:30:00奥运会纪念徽章:网球
日期:2008-07-01 09:01:50ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
发表于 2011-4-12 18:06 | 显示全部楼层
悲催了,我也没去重。。。。

使用道具 举报

回复
论坛徽章:
32
祖国60周年纪念徽章
日期:2009-10-09 08:28:002013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2013-06-28 11:09:23ITPUB季度 技术新星
日期:2013-07-30 16:04:58优秀写手
日期:2013-12-18 09:29:132014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09红孩儿
日期:2014-03-04 16:40:38美羊羊
日期:2015-02-16 16:36:28懒羊羊
日期:2015-03-04 14:52:11
发表于 2011-4-12 18:09 | 显示全部楼层
以这个新增条件来作为评分标准太不合理了,死在这个上我觉得有点冤,毕竟大家都是花了不少时间的

使用道具 举报

回复
论坛徽章:
8
玉兔
日期:2015-11-16 10:18:00铁扇公主
日期:2015-10-27 21:47:42九尾狐狸
日期:2015-12-11 22:31:15
发表于 2011-4-12 18:20 | 显示全部楼层
啊。。。。。。。。

SQL4-27 不就是标准答案最终版的解法吗?怎么分数还这么低啊?

使用道具 举报

回复
论坛徽章:
8
玉兔
日期:2015-11-16 10:18:00铁扇公主
日期:2015-10-27 21:47:42九尾狐狸
日期:2015-12-11 22:31:15
发表于 2011-4-12 18:22 | 显示全部楼层
比拆分到天的做法还差?评委这不是自己在否定自己?

使用道具 举报

回复

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

本版积分规则 发表回复

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