ITPUB“盛拓传媒杯”SQL数据库编程大赛最后一题，答题结束，进入评审期！

~贝贝~

整40人参与了答题，答题参与人的名单罗列在本贴二楼，现在进入评审期，第三期的评审在清明假期后将会公布。第四期的评审预计于4月8日之前完成，完成后将尽快公布获奖人信息，好立即给大家派送礼品，以及发送架构师大会参会门票！


盛拓传媒是中国领先的互联网传媒集团，以传媒和互联网的力量，帮助消费者提高生活品质和工作效率是其使命。盛拓传媒略投资者是澳大利亚电信集团，旗下拥有四家享有盛誉的专业垂直网站，涉及IT和汽车两大消费领域（IT168、泡泡网、车168、汽车之家）。集团目前有逾千名员工，在全国各个主要城市均设有分支机构，在IT和汽车领域广泛、精准地覆盖所有目标群体，有效用户数量最多、用户人群层次最丰富，拥有无可比拟的传播力和影响力，是中国最具价值的互联网专业传媒航母。

第一届“盛拓传媒杯”SQL数据库编程大赛宗旨并非考核脑筋急转弯或高深的算法考核各位puber的算法能力，而且主要以PL/SQL的技巧和技能，以及对SQL新功能的掌握能力，性能优化能力，并附带考核对数据库体系结构的综合理解。题目均以oracle及MS SQL Server数据库为平台。

本次大赛将分四期进行，每轮为答题期为1周，对每期竞赛的参与者，评委将会公正的给予打分，累积四期大赛的得分最高的TOP10将赠予礼品一份、数据库技术大会参会门票一张及授予“ITPUB SQL数据库编程大师”的称号。具体奖项和礼品说明如下：
TOP1-3：赠予IPOD touch 8G一个、2011数据库技术大会B票一张、并授予“ITPUB SQL数据库编程大师”的称号，奖励专属徽章以标识身份；
TOP4-10：赠予东芝 500G移动硬盘一个，2011数据库技术大会B票一张、并授予“ITPUB SQL数据库编程大师”的称号，奖励专属徽章以标识身份；
参与奖：每位参与的会员均可获赠有盛拓传媒的纪念品一份，所有参与的会员均可得到一枚参与徽章。
特别奖：此奖将从每期参与的puber中根据答题情况评选出2-3名不等的特别奖（例如：特别勤奋奖，特别创意奖等等，根据当期情况不同，奖项不同），赠予itpub技术丛书一册/itpub9周年保温杯一个（任选其一）。此奖项也将和以上奖项同时公布。

现在开始“盛拓传媒杯”SQL大赛最后一期 第四期答题，答题时间为：2011年3月25日-4月1日24：00结束，请大家在此时间内答题。

本期题目：
某项房产资源服务按天数、人数收费。收费标准与那一天的用户数有关，对于来自同一个房产经纪公司的用户，某天的总用户数越多则折扣越多。

1. CREATE TABLE services ( -------- 各项服务
   
2.        service_id    NUMBER PRIMARY KEY
   
3.       ,service_name  VARCHAR2(20)
   
4.       );
   
5. CREATE TABLE companies ( -------- 使用服务的公司
   
6.        company_id     NUMBER PRIMARY KEY
   
7.       ,company_name   VARCHAR2(40)
   
8.       );
   
9.      
   
10. CREATE TABLE service_users ( -------公司下的用户（房产经纪）
    
11.        user_id     NUMBER PRIMARY KEY
    
12.       ,company_id  NUMBER NOT NULL REFERENCES companies(company_id)
    
13.       ,user_name   VARCHAR2(20)
    
14.       );
    
15. CREATE TABLE service_usage (
    
16. ------- 使用情况，起止日为闭合区间（首尾包含），如果只用一天，则start_date=end_date
    
17. ------  该表为大表，数据较多
    
18.        usage_id    NUMBER PRIMARY KEY
    
19.       ,user_id     NUMBER NOT NULL REFERENCES service_users(user_id)
    
20.       ,service_id  NUMBER NOT NULL REFERENCES services(service_id)
    
21.       ,start_date  DATE   NOT NULL -------- 数据只含日期，不含时间
    
22.       ,end_date    DATE   NOT NULL -------- 数据只含日期，不含时间
    
23.       ,CONSTRAINT check_dates CHECK (end_date>=start_date)
    
24.       );
    
25. CREATE TABLE service_rates ( -------收费标准，每个公司各不相同, 按人数段计费
    
26.        service_id     NUMBER NOT NULL REFERENCES services(service_id)
    
27.       ,company_id     NUMBER NOT NULL REFERENCES companies(company_id)
    
28.       ,category_id    NUMBER NOT NULL ------ 1,2,3,4表示1档，2档，3档，4档
    
29.       ,user_count_min NUMBER NOT NULL ------ 该档人数下限，1档从1开始
    
30.       ,user_count_max NUMBER NOT NULL ------ 该档人数上限，同样是闭合区间。最后一档不妨设为99999999
    
31.       ,rate           NUMBER NOT NULL ------ 该档的费率，乘以天数再乘以人数就是总费用
    
32.       ,PRIMARY KEY (company_id,service_id,category_id)
    
33.       );
    
34. 输入：
    
35. 起止日期(yyyymmdd字符串), 闭合区间（首尾包含）, 如果只计算一天，则p_start_date=p_end_date
    
36. 绑定变量:
    
37. VAR p_start_date VARCHAR2(8);
    
38. VAR p_end_date VARCHAR2(8);
    
39. 输出：费用报表
    
40. company_id,company_name,service_id,service_name,fee1,fee2,fee3,fee4,total_fee
    
41. 其中:fee1,fee2,fee3,fee4分别表示1档，2档，3档，4档的费用,total_fee是上述4项的总和。若存在某档无数据，则总和为NULL。
    
42. 按照company_id,service_id排序(升序)。

复制代码

测试数据：

1. INSERT INTO services VALUES(1,'Service A');
   
2. INSERT INTO services VALUES(2,'Service B');
   
3. INSERT INTO services VALUES(3,'Service C');
   
4. INSERT INTO companies VALUES(1,'company A');
   
5. INSERT INTO companies VALUES(2,'company B');
   
6. INSERT INTO companies VALUES(3,'company C');
   
7. INSERT INTO service_users VALUES(101,1,'User 1 Comp A');
   
8. INSERT INTO service_users VALUES(102,1,'User 2 Comp A');
   
9. INSERT INTO service_users VALUES(103,1,'User 3 Comp A');
   
10. INSERT INTO service_users VALUES(104,1,'User 4 Comp A');
    
11. INSERT INTO service_users VALUES(201,2,'User 1 Comp B');
    
12. INSERT INTO service_users VALUES(202,2,'User 2 Comp B');
    
13. INSERT INTO service_users VALUES(203,2,'User 3 Comp B');
    
14. INSERT INTO service_users VALUES(204,2,'User 4 Comp B');
    
15. INSERT INTO service_users VALUES(301,3,'User 1 Comp C');
    
16. INSERT INTO service_users VALUES(302,3,'User 2 Comp C');
    
17. INSERT INTO service_users VALUES(303,3,'User 3 Comp C');
    
18. insert into service_rates values (1,1,1,1,1,20);
    
19. insert into service_rates values (1,1,2,2,2,15);
    
20. insert into service_rates values (1,1,3,3,3,10);
    
21. insert into service_rates values (1,1,4,4,99999999,5);
    
22. insert into service_rates values (2,1,1,1,1,30);
    
23. insert into service_rates values (2,1,2,2,2,25);
    
24. insert into service_rates values (2,1,3,3,3,20);
    
25. insert into service_rates values (2,1,4,4,99999999,15);
    
26. insert into service_rates values (1,2,1,1,1,22);
    
27. insert into service_rates values (1,2,2,2,2,17);
    
28. insert into service_rates values (1,2,3,3,3,12);
    
29. insert into service_rates values (1,2,4,4,99999999,8);
    
30. insert into service_rates values (2,2,1,1,1,28);
    
31. insert into service_rates values (2,2,2,2,2,21);
    
32. insert into service_rates values (2,2,3,3,3,17);
    
33. insert into service_rates values (2,2,4,4,99999999,12);
    
34. INSERT INTO service_usage VALUES(1,101,1,DATE '2010-1-5' ,DATE '2010-1-10');
    
35. INSERT INTO service_usage VALUES(2,102,1,DATE '2010-1-3' ,DATE '2010-1-11');
    
36. INSERT INTO service_usage VALUES(3,103,1,DATE '2010-1-7' ,DATE '2010-1-10');
    
37. INSERT INTO service_usage VALUES(4,104,1,DATE '2010-1-10',DATE '2010-1-26');
    
38. INSERT INTO service_usage VALUES(11,101,2,DATE '2010-1-1' ,DATE '2010-1-10');
    
39. INSERT INTO service_usage VALUES(12,102,2,DATE '2010-1-2' ,DATE '2010-1-8' );
    
40. INSERT INTO service_usage VALUES(13,103,2,DATE '2010-1-7' ,DATE '2010-1-10');
    
41. INSERT INTO service_usage VALUES(14,104,2,DATE '2010-1-5' ,DATE '2010-1-13');
    
42. INSERT INTO service_usage VALUES(21,201,1,DATE '2010-1-2' ,DATE '2010-1-21');
    
43. INSERT INTO service_usage VALUES(22,202,1,DATE '2010-1-1' ,DATE '2010-1-17');
    
44. INSERT INTO service_usage VALUES(23,203,1,DATE '2010-1-7' ,DATE '2010-1-13');
    
45. INSERT INTO service_usage VALUES(24,204,1,DATE '2010-1-3' ,DATE '2010-1-30');
    
46. INSERT INTO service_usage VALUES(31,201,2,DATE '2010-1-1' ,DATE '2010-1-2');
    
47. INSERT INTO service_usage VALUES(32,202,2,DATE '2010-1-2' ,DATE '2010-1-3');
    
48. INSERT INTO service_usage VALUES(33,203,2,DATE '2010-1-4' ,DATE '2010-1-10');
    
49. INSERT INTO service_usage VALUES(34,204,2,DATE '2010-1-11',DATE '2010-1-13');
    
50. commit;

复制代码

答题注意：
1.选手必须按照题目给定的表结构答题，否则不得分
2.应提供总体思路和关键步骤的汉字注释
3.并提供样例数据对如下输入变量的输出结果，以便验证

1. VAR p_start_date VARCHAR2(8);
   
2. VAR p_end_date VARCHAR2(8);
   
3. EXEC :p_start_date := '20100104';
   
4. EXEC :p_end_date := '20100130';

复制代码

4.要求用一个SQL查询语句完成（可以包括子查询，但不能包括ddl和dml语句）

第四期活动评委：〇〇, newkid, dingjun123, bq_wang、lastwinner

参与规则：
每个参赛者以附件方式贴出自己的源代码，并将阅读权限设为11～100之间的数值，答案贴出后不允许编辑帖子。编辑过的帖子做无效处理。需要修改答案请发表新的回复，并以同样的方式上传新的答案。

附件名称以SQL大赛第N期(ID).txt来命名，通过rar压缩加密，推荐采取select sys_guid() from dual的方式产生密码，答题者需要在截止时间前将密码以论坛PM给论坛助理~贝贝~
pm链接：http://www.itpub.net/pm.php?action=send&uid=93405

此次活动也非常欢迎新注册会员参与，但因新注册会员三天内均在临时用户组，故不能上传附件，所以请参与此次活动的新会员，把答题压缩包和解压密码直接发邮件给我即可：wangbei@it168.com，我会在收到后第一时间给予您回复的。

特别申明：在答题期间不准在论坛公开讨论与此题有关的解题思路！

活动流程：
1、发布本期活动题目及答题时间范围，大家可以在此期间根据以上参与规则上传自己的参赛程序文档。
2、结束答题，进入评审阶段，评审将在一周内对所有参赛程序进行评审，评审结束后将公布评审及作品点评。

评审规则：
1、由ITPUB论坛助理下载参赛者附件并解压后删除参赛者ID,将各参赛文档编入代号，并发给当期评委。
2、评委抽签获得序号并评审相应序号的答案。运行程序，将运行出正确答案的参赛文档选出。
3、所有评委独立对运行出正确答案的程序打分，把相应分数告知论坛助理。论坛助理取平均数为该答案最后得分。
注：出题者负责向评委讲解思路，并配合评委书写点评。

第三期的答题结果，预计于4月6日公布，评委们在此期间会抓紧时间对大家的答题进行评审和点评给予建议！现在请大家继续支持第四期的活动，参与答题吧，无论语句写的好坏，参与其中必然得到最多收获，期待更多的puber参与到活动中！

~贝贝~

活动最新进展：
第三期活动评审：正在进行中，预计于4月6日公布评审结果

SQL大赛活动相关链接，欢迎大家查看并了解此次活动：
第三期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-1411980-1-1.html
SQL数据库编程大赛第一期评分及所有答题公布链接：http://www.itpub.net/thread-1407072-1-1.html
从SQL比赛第一期解答中引出的问题：http://www.itpub.net/thread-1407195-1-2.html
TKV5-ITPUB SQL大赛第一期赏析：http://www.itpub.net/thread-1407361-1-2.html
继续挑战: 被〇〇毙掉的题目!：http://www.itpub.net/thread-1407879-1-1.html
[随便谈谈]ITPUB第一届SQL大赛第一期的共性问题：http://www.itpub.net/thread-1406578-1-1.html

第四期参与者名单，不定时更新如下，目前已有41位puber提交了答题：
qqjue
jvkojvko
myfriend2010
renxiao2003
ai2ming
Arsenal-8
xgghxkhuang
yzsind
old_voldo
lk-2010
wenhuiqiao
junsansi
6666444
szusunny
oracle_chang
wangkxxe
jiqing1004
fnghost
cui6548
solomon_007
tang870
accelerator
mychary
lxddxl
AC米兰
yu_dongyang
luoyoumou
regonly1
victor666666
todayz
jxmarvel
yaots
hongyehongye1
rollingpig
northmoon
3833020
ruideliang
Nalternative
yangtingkun
my_legend
bavol2010

lastwinner

嗯，答疑楼

ling2995

看看！

qqjue

费率是按公司,服务决定,某一天的费率是按这天这个公司的总人数算,还是这天这个公司这项服务的总人数算的.
有的人一天有两项服务,是不是这个人要算两项服务的钱?

[ 本帖最后由 qqjue 于 2011-3-25 19:02 编辑 ]

lugionline

fee1,fee2,fee3,fee4分别表示1档，2档，3档，4档的费用,total_fee是上述4项的总和。若存在某档无数据，则总和为NULL。

“则总和为NULL” 这个是说 total_fee 是 null还是 fee1-fee4是null?

lugionline

给个T-SQL的建表脚本和测试数据吧，这题目好像没什么好发挥的了...

〇〇

mssql把number修改为bigint

jboracle1981

顶！观望！

newkid

费率是按公司,服务决定,某一天的费率是按这天这个公司的总人数算,还是这天这个公司这项服务的总人数算的.

按使用该项服务的人数算。在这一天没有使用该服务的其他人，不予收费当然也不能参与计算折扣。

有的人一天有两项服务,是不是这个人要算两项服务的钱?

是的，每项服务独立收费。

fee1,fee2,fee3,fee4分别表示1档，2档，3档，4档的费用,total_fee是上述4项的总和。若存在某档无数据，则总和为NULL。

“则总和为NULL” 这个是说 total_fee 是 null还是 fee1-fee4是null?

仅仅在该档次为NULL; 如果fee1-fee4全部为NULL则TOTAL_FEE为零。（纠正：如果四项服务全未使用过则这一行不需要输出，而不是TOTAL_FEE=0）

[ 本帖最后由 newkid 于 2011-3-25 21:46 编辑 ]