一道难度很大的sql题目和解答

来自ORACLE论坛里的一道SQL题目。大家分析看看国外的牛人是怎么写SQL的。

 

SQL> ed
I have the following data...

ID START_DATE END_DATE CONSUMPTION
1 1/1/09 3/15/09 50
2 3/15/09 4/22/09 30
3 4/22/09 6/20/09 45

 

I need to select the data, so that I can split each of the rows into the months that make up each row with the first date of the month and the last day of the month unless the start and end dates are inbetween the months.

 

My result should look something like this...

ID START_DATE END_DATE
1 1/1/09 1/31/09
1 2/1/09 2/28/09
1 3/1/09 3/15/09
2 3/15/09 3/31/09
2 4/1/09 4/22/09
3 4/22/09 5/1/09
3 5/1/09 5/30/09
3 6/1/09 6/20/09

I know I can do this programattically, but I would rather do this in SQL so that I can build views/materialized views from the SQL.

Thanks for any help you may be able to offer.

Joe

 

 

 

answer：
在plsql里执行的语句
 
with t as (select 1 as ID, date '2009-01-01' as START_DATE, date '2009-03-15' as END_DATE, 50 as CONSUMPTION from dual union all
            select 2, date '2009-03-15', date '2009-04-22', 30 from dual union all
            select 3, date '2009-04-22', date '2009-06-20', 45 from dual)
select id ,rn,
case when trunc(add_months(start_date, rn-1),'MM') < start_date then start_date else trunc(add_months(start_date,rn-1),'MM') end as start_date,
case when trunc(add_months(start_date, rn),'MM')-1 > end_date then end_date else trunc(add_months(start_date, rn),'MM')-1 end as end_date
from t,
(
select rownum as rn
from dual connect by rownum <=
(
select ceil(max(months_between(end_date, start_date)))
from t
)
)
where case when trunc(add_months(start_date, rn-1),'MM') < start_date then start_date else trunc(add_months(start_date,rn-1),'MM') end   between start_date and end_date
  order by 1, 2

 

        ID START_DATE           END_DATE
---------- -------------------- --------------------
         1 01-JAN-2009 00:00:00 31-JAN-2009 00:00:00
         1 01-FEB-2009 00:00:00 28-FEB-2009 00:00:00
         1 01-MAR-2009 00:00:00 15-MAR-2009 00:00:00
         2 15-MAR-2009 00:00:00 31-MAR-2009 00:00:00
         2 01-APR-2009 00:00:00 22-APR-2009 00:00:00
         3 22-APR-2009 00:00:00 30-APR-2009 00:00:00
         3 01-MAY-2009 00:00:00 31-MAY-2009 00:00:00
         3 01-JUN-2009 00:00:00 20-JUN-2009 00:00:00
 
8 rows selected.