我数据表中计划是按月存的，我想拆分到每一天，怎么写？
月ID 单位ID 类型1 计划量
201110 1 1 300
我想拆分后得到日 单位ID 类型1 计划量
20111001 1 1 300/31
20111002 1 1 300/31
月ID 单位ID 类型1 计划量
201110 1 1 300
我想拆分后得到日 单位ID 类型1 计划量
20111001 1 1 300/31
20111002 1 1 300/31
with tbl as
(
select '201102' as smonth, 1 as iid, 1 as itype, 560 as icount from dual
union all
select '201110' as smonth, 2 as iid, 2 as itype, 310 as icount from dual
)
select smonth || sday as sdate, iid, itype,
icount / to_number(smaxday) as icount
from (select tbl.*, to_char(last_day(to_date(smonth, 'yyyymm')),'dd') as smaxday
from tbl) t1,
(select lpad(to_char(rownum), 2, '0') as sday from dual
connect by rownum <= 31) t2
where t1.smaxday >= t2.sday
order by sdate;
SQL> WITH tb AS (
2 SELECT 201102 month_id,0 company_id,0 type_id,500 plan_amount FROM DUAL UNION ALL
3 SELECT 201110 month_id,1 company_id,1 type_id,300 plan_amount FROM DUAL UNION ALL
4 SELECT 201111 month_id,2 company_id,2 type_id,600 plan_amount FROM DUAL
5 )
6 SELECT TO_DATE(t1.month_id, 'yyyymm') + t2.rn - 1 every_day,
7 t1.company_id,
8 t1.type_id,
9 plan_amount / (LAST_DAY(TO_DATE(t1.month_id, 'yyyymm')) -
10 TO_DATE(t1.month_id, 'yyyymm')) avg_amount
11 FROM tb t1,
12 (SELECT ROWNUM rn,
13 lastday
14 FROM (SELECT MAX(LAST_DAY(TO_DATE(tb.month_id, 'yyyymm')) -
15 TO_DATE(tb.month_id, 'yyyymm')) + 1 lastday
16 FROM tb)
17 CONNECT BY ROWNUM <= lastday) t2
18 WHERE LAST_DAY(TO_DATE(t1.month_id, 'yyyymm')) >=
19 TO_DATE(t1.month_id, 'yyyymm') + t2.rn - 1
20 ORDER BY t1.month_id,
21 t2.rn
22 ;EVERY_DAY COMPANY_ID TYPE_ID AVG_AMOUNT
----------- ---------- ---------- ----------
2011/02/01 0 0 18.5185185
2011/02/02 0 0 18.5185185
2011/02/03 0 0 18.5185185
2011/02/04 0 0 18.5185185
2011/02/05 0 0 18.5185185
2011/02/06 0 0 18.5185185
2011/02/07 0 0 18.5185185
2011/02/08 0 0 18.5185185
2011/02/09 0 0 18.5185185
2011/02/10 0 0 18.5185185
2011/02/11 0 0 18.5185185
2011/02/12 0 0 18.5185185
2011/02/13 0 0 18.5185185
2011/02/14 0 0 18.5185185
2011/02/15 0 0 18.5185185
2011/02/16 0 0 18.5185185
2011/02/17 0 0 18.5185185
2011/02/18 0 0 18.5185185
2011/02/19 0 0 18.5185185
2011/02/20 0 0 18.5185185EVERY_DAY COMPANY_ID TYPE_ID AVG_AMOUNT
----------- ---------- ---------- ----------
2011/02/21 0 0 18.5185185
2011/02/22 0 0 18.5185185
2011/02/23 0 0 18.5185185
2011/02/24 0 0 18.5185185
2011/02/25 0 0 18.5185185
2011/02/26 0 0 18.5185185
2011/02/27 0 0 18.5185185
2011/02/28 0 0 18.5185185
2011/10/01 1 1 10
2011/10/02 1 1 10
2011/10/03 1 1 10
2011/10/04 1 1 10
2011/10/05 1 1 10
2011/10/06 1 1 10
2011/10/07 1 1 10
2011/10/08 1 1 10
2011/10/09 1 1 10
2011/10/10 1 1 10
2011/10/11 1 1 10
2011/10/12 1 1 10
2011/10/13 1 1 10EVERY_DAY COMPANY_ID TYPE_ID AVG_AMOUNT
----------- ---------- ---------- ----------
2011/10/14 1 1 10
2011/10/15 1 1 10
2011/10/16 1 1 10
2011/10/17 1 1 10
2011/10/18 1 1 10
2011/10/19 1 1 10
2011/10/20 1 1 10
2011/10/21 1 1 10
2011/10/22 1 1 10
2011/10/23 1 1 10
2011/10/24 1 1 10
2011/10/25 1 1 10
2011/10/26 1 1 10
2011/10/27 1 1 10
2011/10/28 1 1 10
2011/10/29 1 1 10
2011/10/30 1 1 10
2011/10/31 1 1 10
2011/11/01 2 2 20.6896551
2011/11/02 2 2 20.6896551
2011/11/03 2 2 20.6896551EVERY_DAY COMPANY_ID TYPE_ID AVG_AMOUNT
----------- ---------- ---------- ----------
2011/11/04 2 2 20.6896551
2011/11/05 2 2 20.6896551
2011/11/06 2 2 20.6896551
2011/11/07 2 2 20.6896551
2011/11/08 2 2 20.6896551
2011/11/09 2 2 20.6896551
2011/11/10 2 2 20.6896551
2011/11/11 2 2 20.6896551
2011/11/12 2 2 20.6896551
2011/11/13 2 2 20.6896551
2011/11/14 2 2 20.6896551
2011/11/15 2 2 20.6896551
2011/11/16 2 2 20.6896551
2011/11/17 2 2 20.6896551
2011/11/18 2 2 20.6896551
2011/11/19 2 2 20.6896551
2011/11/20 2 2 20.6896551
2011/11/21 2 2 20.6896551
2011/11/22 2 2 20.6896551
2011/11/23 2 2 20.6896551
2011/11/24 2 2 20.6896551EVERY_DAY COMPANY_ID TYPE_ID AVG_AMOUNT
----------- ---------- ---------- ----------
2011/11/25 2 2 20.6896551
2011/11/26 2 2 20.6896551
2011/11/27 2 2 20.6896551
2011/11/28 2 2 20.6896551
2011/11/29 2 2 20.6896551
2011/11/30 2 2 20.689655189 rows selected
SQL> with tbl as
2 (
3 select '201102' as smonth, 1 as iid, 1 as itype, 560 as icount from dual
4 union all
5 select '201110' as smonth, 2 as iid, 2 as itype, 310 as icount from dual
6 )
7 select smonth || sday as sdate, iid, itype,
8 icount / to_number(smaxday) as icount
9 from (select tbl.*, to_char(last_day(to_date(smonth, 'yyyymm')),'dd') as smaxday
10 from tbl) t1,
11 (select lpad(to_char(rownum), 2, '0') as sday from dual
12 connect by rownum <= 31) t2
13 where t1.smaxday >= t2.sday
14 order by sdate;SDATE IID ITYPE ICOUNT
-------------- ---------- ---------- ----------
20110201 1 1 20
20110202 1 1 20
20110203 1 1 20
20110204 1 1 20
20110205 1 1 20
20110206 1 1 20
20110207 1 1 20
20110208 1 1 20
20110209 1 1 20
20110210 1 1 20
20110211 1 1 20
20110212 1 1 20
20110213 1 1 20
20110214 1 1 20
20110215 1 1 20
20110216 1 1 20
20110217 1 1 20
20110218 1 1 20
20110219 1 1 20
20110220 1 1 20SDATE IID ITYPE ICOUNT
-------------- ---------- ---------- ----------
20110221 1 1 20
20110222 1 1 20
20110223 1 1 20
20110224 1 1 20
20110225 1 1 20
20110226 1 1 20
20110227 1 1 20
20110228 1 1 20
20111001 2 2 10
20111002 2 2 10
20111003 2 2 10
20111004 2 2 10
20111005 2 2 10
20111006 2 2 10
20111007 2 2 10
20111008 2 2 10
20111009 2 2 10
20111010 2 2 10
20111011 2 2 10
20111012 2 2 10
20111013 2 2 10SDATE IID ITYPE ICOUNT
-------------- ---------- ---------- ----------
20111014 2 2 10
20111015 2 2 10
20111016 2 2 10
20111017 2 2 10
20111018 2 2 10
20111019 2 2 10
20111020 2 2 10
20111021 2 2 10
20111022 2 2 10
20111023 2 2 10
20111024 2 2 10
20111025 2 2 10
20111026 2 2 10
20111027 2 2 10
20111028 2 2 10
20111029 2 2 10
20111030 2 2 10
20111031 2 2 1059 rows selected
11月有31号？？？？？