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

jvkojvko · 发表于 2011-3-28 20:13

service_users的数据是这样构建的

Select * From (
Select trim(company_id)||trim(lv1) a,company_id,'user'||lv1||' '||company_name b From
companies,(
Select to_char(Level,'09') lv1 From dual
Connect By Level <100
)
)
a
Where Not Exists (Select 1 From service_users Where a = user_id And a.company_id =service_users.company_id )

jvkojvko · 发表于 2011-3-28 20:13

service_rates 是这样构建的

Create Table tmp1231
As
Select service_id,company_id,category_id,user_count_min,user_count_max,rate From services,
companies,(Select category_id,user_count_min,user_count_max,rate From service_rates Where service_id = 1 And company_id = 1)

Truncate Table service_rates

Insert Into service_rates
Select * From tmp1231

jvkojvko · 发表于 2011-3-28 20:14

service_usage的数据是这样构建的

Truncate Table service_usage

Insert Into service_usage
Select user_id+Rownum*1000,user_id,service_id,trunc(Sysdate)+trunc(dbms_random.Value(0,180)),trunc(Sysdate)+trunc(dbms_random.Value(180,365))
From service_users,services

jvkojvko · 发表于 2011-3-28 20:15

SQL> Select Min(start_date) From service_usage;

MIN(START_DATE)
---------------
2011-3-28

SQL>
SQL> Select Max(end_date) From service_usage;

MAX(END_DATE)
-------------
2012-3-26

SQL>