本期题目：某项房产资源服务按天数、人数收费。收费标准与那一天的用户数有关，对于来自同一个房产经纪公司的用户，某天的总用户数越多则折扣越多。
CODE: CREATE TABLE services ( -------- 各项服务 service_id NUMBER PRIMARY KEY ,service_name VARCHAR2(20) ); CREATE TABLE companies ( -------- 使用服务的公司 company_id NUMBER PRIMARY KEY ,company_name VARCHAR2(40) ); CREATE TABLE service_users ( -------公司下的用户（房产经纪） user_id NUMBER PRIMARY KEY ,company_id NUMBER NOT NULL REFERENCES companies(company_id) ,user_name VARCHAR2(20) ); CREATE TABLE service_usage ( ------- 使用情况，起止日为闭合区间（首尾包含），如果只用一天，则start_date=end_date ------ 该表为大表，数据较多 usage_id NUMBER PRIMARY KEY ,user_id NUMBER NOT NULL REFERENCES service_users(user_id) ,service_id NUMBER NOT NULL REFERENCES services(service_id) ,start_date DATE NOT NULL -------- 数据只含日期，不含时间 ,end_date DATE NOT NULL -------- 数据只含日期，不含时间 ,CONSTRAINT check_dates CHECK (end_date>=start_date) ); CREATE TABLE service_rates ( -------收费标准，每个公司各不相同, 按人数段计费 service_id NUMBER NOT NULL REFERENCES services(service_id) ,company_id NUMBER NOT NULL REFERENCES companies(company_id) ,category_id NUMBER NOT NULL ------ 1,2,3,4表示1档，2档，3档，4档 ,user_count_min NUMBER NOT NULL ------ 该档人数下限，1档从1开始 ,user_count_max NUMBER NOT NULL ------ 该档人数上限，同样是闭合区间。最后一档不妨设为99999999 ,rate NUMBER NOT NULL ------ 该档的费率，乘以天数再乘以人数就是总费用 ,PRIMARY KEY (company_id,service_id,category_id) ); 输入： 起止日期(yyyymmdd字符串), 闭合区间（首尾包含）, 如果只计算一天，则p_start_date=p_end_date 绑定变量: VAR p_start_date VARCHAR2(8); VAR p_end_date VARCHAR2(8); 输出：费用报表 company_id,company_name,service_id,service_name,fee1,fee2,fee3,fee4,total_fee 其中:fee1,fee2,fee3,fee4分别表示1档，2档，3档，4档的费用,total_fee是上述4项的总和。若存在某档无数据，则总和为NULL。 按照company_id,service_id排序(升序)。测试数据：
CODE:
INSERT INTO services VALUES(1,'Service A'); INSERT INTO services VALUES(2,'Service B'); INSERT INTO services VALUES(3,'Service C'); INSERT INTO companies VALUES(1,'company A'); INSERT INTO companies VALUES(2,'company B'); INSERT INTO companies VALUES(3,'company C'); INSERT INTO service_users VALUES(101,1,'User 1 Comp A'); INSERT INTO service_users VALUES(102,1,'User 2 Comp A'); INSERT INTO service_users VALUES(103,1,'User 3 Comp A'); INSERT INTO service_users VALUES(104,1,'User 4 Comp A'); INSERT INTO service_users VALUES(201,2,'User 1 Comp B'); INSERT INTO service_users VALUES(202,2,'User 2 Comp B'); INSERT INTO service_users VALUES(203,2,'User 3 Comp B'); INSERT INTO service_users VALUES(204,2,'User 4 Comp B'); INSERT INTO service_users VALUES(301,3,'User 1 Comp C'); INSERT INTO service_users VALUES(302,3,'User 2 Comp C'); INSERT INTO service_users VALUES(303,3,'User 3 Comp C'); insert into service_rates values (1,1,1,1,1,20); insert into service_rates values (1,1,2,2,2,15); insert into service_rates values (1,1,3,3,3,10); insert into service_rates values (1,1,4,4,99999999,5); insert into service_rates values (2,1,1,1,1,30); insert into service_rates values (2,1,2,2,2,25); insert into service_rates values (2,1,3,3,3,20); insert into service_rates values (2,1,4,4,99999999,15); insert into service_rates values (1,2,1,1,1,22); insert into service_rates values (1,2,2,2,2,17); insert into service_rates values (1,2,3,3,3,12); insert into service_rates values (1,2,4,4,99999999,8); insert into service_rates values (2,2,1,1,1,28); insert into service_rates values (2,2,2,2,2,21); insert into service_rates values (2,2,3,3,3,17); insert into service_rates values (2,2,4,4,99999999,12); INSERT INTO service_usage VALUES(1,101,1,DATE '2010-1-5' ,DATE '2010-1-10'); INSERT INTO service_usage VALUES(2,102,1,DATE '2010-1-3' ,DATE '2010-1-11'); INSERT INTO service_usage VALUES(3,103,1,DATE '2010-1-7' ,DATE '2010-1-10'); INSERT INTO service_usage VALUES(4,104,1,DATE '2010-1-10',DATE '2010-1-26'); INSERT INTO service_usage VALUES(11,101,2,DATE '2010-1-1' ,DATE '2010-1-10'); INSERT INTO service_usage VALUES(12,102,2,DATE '2010-1-2' ,DATE '2010-1-8' ); INSERT INTO service_usage VALUES(13,103,2,DATE '2010-1-7' ,DATE '2010-1-10'); INSERT INTO service_usage VALUES(14,104,2,DATE '2010-1-5' ,DATE '2010-1-13'); INSERT INTO service_usage VALUES(21,201,1,DATE '2010-1-2' ,DATE '2010-1-21'); INSERT INTO service_usage VALUES(22,202,1,DATE '2010-1-1' ,DATE '2010-1-17'); INSERT INTO service_usage VALUES(23,203,1,DATE '2010-1-7' ,DATE '2010-1-13'); INSERT INTO service_usage VALUES(24,204,1,DATE '2010-1-3' ,DATE '2010-1-30'); INSERT INTO service_usage VALUES(31,201,2,DATE '2010-1-1' ,DATE '2010-1-2'); INSERT INTO service_usage VALUES(32,202,2,DATE '2010-1-2' ,DATE '2010-1-3'); INSERT INTO service_usage VALUES(33,203,2,DATE '2010-1-4' ,DATE '2010-1-10'); INSERT INTO service_usage VALUES(34,204,2,DATE '2010-1-11',DATE '2010-1-13'); commit;答题注意：1.选手必须按照题目给定的表结构答题，否则不得分2.应提供总体思路和关键步骤的汉字注释3.并提供样例数据对如下输入变量的输出结果，以便验证
CODE: VAR p_start_date VARCHAR2(8); VAR p_end_date VARCHAR2(8); EXEC :p_start_date := '20100104'; EXEC :p_end_date := '20100130';4.要求用一个SQL查询语句完成（可以包括子查询，但不能包括ddl和dml语句）
原文见：http://www.itpub.net/thread-1411495-1-1.html
参赛者答案：http://www.itpub.net/thread-1417576-1-1.html
我提交的答案：
/*ITPUB“盛拓传媒杯”SQL数据库编程大赛第4题数据库类型：Oracle,任意版本解题思路：1、先根据service_usage生成每天一条记录 select to_date(:p_start_date, 'YYYYMMDD') + rownum - 1 mydate from dual connect by rownum <=to_date(:p_end_date, 'YYYYMMDD') - to_date(:p_start_date, 'YYYYMMDD') + 1 上面这个子查询与service_usage关联(mydate between a.start_date and a.end_date)可以生成每天一笔的消费记录 count(distinct a.user_id) distinct_user_cnt 表示不同的用户数，如果1个用户1天消费多次算1个用户，根据这个数记算档次 count(*) user_servcie_cnt 表示一天所有用户服务次数2、与service_rates关联确定消费档次，按company_id, service_id，category_id汇总生成fee1,fee2,fee3,fee4,total_fee3、与services关联得到service_name,与companies关联得到company_nameuse_hash(f,g,h),use_hash(d,e),use_merge(a,b,c)这些全是为了大数量时优化，小数据量时可以不要。*/
VAR p_start_date VARCHAR2(8); VAR p_end_date VARCHAR2(8); EXEC :p_start_date := '20100104'; EXEC :p_end_date := '20100130'; select /*+use_hash(f,g,h)*/f.company_id,h.company_name,f.service_id,g.service_name,fee1,fee2,fee3,fee4,total_fee from (select /*+use_hash(d,e)*/ d.company_id,d.service_id, sum(decode(e.category_id, 1, rate * user_servcie_cnt, null)) fee1, sum(decode(e.category_id, 2, rate * user_servcie_cnt, null)) fee2, sum(decode(e.category_id, 3, rate * user_servcie_cnt, null)) fee3, sum(decode(e.category_id, 4, rate * user_servcie_cnt, null)) fee4, sum(rate * user_servcie_cnt) total_fee from (select /*+use_merge(a,b,c)*/b.company_id,a.service_id, count(distinct a.user_id) distinct_user_cnt, --表示不同的用户数，如果1个用户1天消费多次算1个用户，根据这个数记算档次 count(*) user_servcie_cnt --表示一天所有用户服务次数 from service_usage a, service_users b, (select to_date(:p_start_date, 'YYYYMMDD') + rownum - 1 mydate from dual connect by rownum <=to_date(:p_end_date, 'YYYYMMDD') - to_date(:p_start_date, 'YYYYMMDD') + 1) c where a.user_id = b.user_id and c.mydate between a.start_date and a.end_date --用于根据service_usage生成每天一笔的消费记录 group by b.company_id, a.service_id, c.mydate) d,service_rates e where d.company_id = e.company_id and d.service_id = e.service_id and d.distinct_user_cnt between e.user_count_min and e.user_count_max --确认消费档次 group by d.company_id, d.service_id) f,services g,companies h --关联显示company_name,service_name where f.company_id = h.company_id and f.service_id = g.service_id order by f.company_id, f.service_id;
--输出结果COMPANY_ID COMPANY_NAME SERVICE_ID SERVICE_NAME FEE1 FEE2 FEE3 FEE4 TOTAL_FEE---------- ---------------------------------------- ---------- -------------------- ---------- ---------- ---------- ---------- ---------- 1 company A 1 Service A 320 90 90 20 520 1 company A 2 Service B 90 50 240 120 500 2 company B 1 Service A 198 136 252 224 810 2 company B 2 Service B 280 280
解题思路：见上面的备注。
评委点评：结构清晰，性能稍差。计费的时候应该使用distinct_user_cnt而不是user_servcie_cnt，这个错误导致重复计费。
个人分析：
1、采用了较笨的按天枚举计算方法，性能在选手里还算可以，但是当数据量大了性能问题严重。
2、题目有歧义，在论坛里很多网友也讨论了，就是当1个客户1天内同一服务使用多次时如何计算？这个对我这种按天枚举计算的算法没什么问题，只是改变一下变量即可，在这点评委扣分较狠，所以分数低了很多。
