问题描述：
表结构如下：
Date_Year Data_Month Data_Day Tjzb_bh zbsj_data
----------------------------------------------------
2010 05 01 0001 100.00
2010 05 02 0001 110.00
2010 05 09 0001 120.00
2010 05 31 0001 130.002010 05 03 0002 60.00
2010 05 10 0002 85.00
2010 05 20 0002 90.00
---------------------------------------------------
对于一个统计指标【Tjzb_bh】每天有且只有1条记录，但每月中可能只有某几天有数据，
如：统计指标【Tjzb_bh=0001】的记录，在2010年05月份只有01、02、09、31这4天有数据；
根据这写记录统计出2010年05月份1~31日的累计数据.
预得到的结果【5月份，统计指标Tjzb_bh=0001】如下：
Date_Year Data_Month Data_Day Tjzb_bh zbsj_data Total
----------------------------------------------------------
2010 05 01 0001 100.00 100.00
2010 05 02 0001 110.00 210.00
2010 05 03 0001 110.00 210.00
2010 05 04 0001 110.00 210.00
2010 05 05 0001 110.00 210.00
...
2010 05 09 0001 120.00 330.00
2010 05 10 0001 120.00 330.00
2010 05 11 0001 120.00 330.00
2010 05 12 0001 120.00 330.00
...
2010 05 31 0001 130.00 460.002010 05 01 0002 00.00 00.00
2010 05 02 0002 00.00 00.00
2010 05 03 0002 60.00 60.00
...
2010 05 10 0002 85.00 145.00
...
2010 05 20 0002 90.00 235.00
...
2010 05 31 0002 00.00 235.00
----------------------------------------------------------
再次诚恳的感谢各位的帮助！！谢谢！！
然后关联后用sum()over()来做累加。
from (
select '2010' Date_Year,'05' date_month,S.rn,S.Tjzb_bh,nvl(t.zbsj_data,0) zbsj_data
from (
select A.rn,B.Tjzb_bh
from (
select rownum rn
from dual
connect by rownum<=31 ) A ,(select distinct Tjzb_bh from t where Date_Year='2010' and Data_Month='05' ) B ) s left join t on S.rn=to_number(t.Data_Day) and s.Tjzb_bh=t.Tjzb_bh and t.Date_Year='2010' and t.date_month='05'
)
没测，试试看
with b as
(
select to_char(sysdate,'yyyy') y, to_char(sysdate,'mm') m , '01' d , '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '02', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '03', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '04', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '05', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '06', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '07', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '08', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '09', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '10', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '11', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '12', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '13', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '14', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '15', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '16', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '17', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '18', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '19', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '20', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '21', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '22', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '23', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '24', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '25', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '26', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '27', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '28', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '29', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '30', '0' fee_1 from dual
union all
select to_char(sysdate,'yyyy'), to_char(sysdate,'mm'), '31', '0' fee_1 from dual
)
select Date_Year, Data_Month ,Data_Day,zbsj_data,Tjzb_bh,sum(zbsj_data) over(order by Tjzb_bh ,Data_Day ) from
(select Date_Year, Data_Month ,Data_Day, Tjzb_bh ,zbsj_data from user_table
union all
select y,m,d,'0001' fee_1 from b where d <= to_char(sysdate - add_months(sysdate ,-1))
union all
select y,m,d,'0002' fee_1 from b where d <= to_char(sysdate - add_months(sysdate ,-1)))
tjzb_bh,
max(v) over(partition by tjzb_bh order by tjzb_bh, num) v1,
sum(v) over(partition by tjzb_bh order by tjzb_bh, num) v2
from (select t3.num,
t3.tjzb_bh,
t4.data_month,
decode(t4.zbsj_data, null, 0, t4.zbsj_data) v
from (select t1.num, t2.tjzb_bh
from (select 1 num from dual
union all
select 2 from dual
union all
select 3 from dual
union all
select 4 from dual
union all
select 5 from dual
union all
select 6 from dual
union all
select 7 from dual
union all
select 8 from dual
union all
select 9 from dual
union all
select 10 from dual
union all
select 11 from dual
union all
select 12 from dual
union all
select 13 from dual
union all
select 14 from dual
union all
select 15 from dual
union all
select 16 from dual
union all
select 17 from dual
union all
select 18 from dual
union all
select 19 from dual
union all
select 20 from dual
union all
select 21 from dual
union all
select 22 from dual
union all
select 23 from dual
union all
select 24 from dual
union all
select 25 from dual
union all
select 26 from dual
union all
select 27 from dual
union all
select 28 from dual
union all
select 29 from dual
union all
select 30 from dual
union all
select 31 from dual
) t1,
(select distinct tjzb_bh
from tab
where date_year = '2010'
and data_month = '05') t2) t3,
tab t4
where t3.num <= to_char(to_date('20100601', 'yyyymmdd') - 1, 'dd')
and t3.tjzb_bh = t4.tjzb_bh(+)
and t3.num = t4.data_day(+)
order by t3.tjzb_bh, t3.num
)
1 2010-05-01 0001 100 100
2 2010-05-02 0001 110 210
3 2010-05-03 0001 110 210
4 2010-05-04 0001 110 210
5 2010-05-05 0001 110 210
6 2010-05-06 0001 110 210
7 2010-05-07 0001 110 210
8 2010-05-08 0001 110 210
9 2010-05-09 0001 120 330
10 2010-05-10 0001 120 330
11 2010-05-11 0001 120 330
12 2010-05-12 0001 120 330
13 2010-05-13 0001 120 330
14 2010-05-14 0001 120 330
15 2010-05-15 0001 120 330
16 2010-05-16 0001 120 330
17 2010-05-17 0001 120 330
18 2010-05-18 0001 120 330
19 2010-05-19 0001 120 330
20 2010-05-20 0001 120 330
21 2010-05-21 0001 120 330
22 2010-05-22 0001 120 330
23 2010-05-23 0001 120 330
24 2010-05-24 0001 120 330
25 2010-05-25 0001 120 330
26 2010-05-26 0001 120 330
27 2010-05-27 0001 120 330
28 2010-05-28 0001 120 330
29 2010-05-29 0001 120 330
30 2010-05-30 0001 120 330
31 2010-05-31 0001 130 460
32 2010-05-01 0002 0 0
33 2010-05-02 0002 0 0
34 2010-05-03 0002 60 60
35 2010-05-04 0002 60 60
36 2010-05-05 0002 60 60
37 2010-05-06 0002 60 60
38 2010-05-07 0002 60 60
39 2010-05-08 0002 60 60
40 2010-05-09 0002 60 60
41 2010-05-10 0002 85 145
42 2010-05-11 0002 85 145
43 2010-05-12 0002 85 145
44 2010-05-13 0002 85 145
45 2010-05-14 0002 85 145
46 2010-05-15 0002 85 145
47 2010-05-16 0002 85 145
48 2010-05-17 0002 85 145
49 2010-05-18 0002 85 145
50 2010-05-19 0002 85 145
51 2010-05-20 0002 90 235
52 2010-05-21 0002 90 235
53 2010-05-22 0002 90 235
54 2010-05-23 0002 90 235
55 2010-05-24 0002 90 235
56 2010-05-25 0002 90 235
57 2010-05-26 0002 90 235
58 2010-05-27 0002 90 235
59 2010-05-28 0002 90 235
60 2010-05-29 0002 90 235
61 2010-05-30 0002 90 235
62 2010-05-31 0002 90 235
SELECT EXT.Y,
EXT.M,
EXT.D,
EXT.B,
SUM(NVL(BASE.ZBSJ_DATA,
0)) OVER(PARTITION BY EXT.Y, EXT.M, EXT.B ORDER BY EXT.D ROWS ROWNUM PRECEDING)
FROM (SELECT *
FROM (SELECT DISTINCT T.TJZB_BH AS B FROM LEISORE T) TMP,
(SELECT TO_NUMBER(TO_CHAR(D,
'YYYY')) AS Y,
TO_NUMBER(TO_CHAR(D,
'MM')) AS M,
TO_NUMBER(TO_CHAR(D,
'DD')) AS D
FROM (SELECT TO_DATE('20100501',
'YYYYMMDD') + ROWNUM - 1 AS D
FROM DBA_OBJECTS
WHERE ROWNUM < (LAST_DAY(TO_DATE('20100501',
'YYYYMMDD')) -
TO_DATE('20100501',
'YYYYMMDD')) + 2))) EXT,
LEISORE BASE
WHERE EXT.Y = BASE.DATE_YEAR(+)
AND EXT.M = BASE.DATA_MONTH(+)
AND EXT.D = BASE.DATA_DAY(+)
AND EXT.B = BASE.TJZB_BH(+)
ORDER BY EXT.Y,
EXT.M,
EXT.B,
EXT.D
没想到咋把zbsj_data列select出来， :(
Date_Year CHAR(4),
Data_Month CHAR(2),
Data_Day CHAR(2),
Tjzb_bh CHAR(4),
zbsj_data NUMBER(18,2)
);INSERT INTO test(Date_Year,Data_Month,Data_Day,Tjzb_bh,zbsj_data) VALUES('2010','05','01','0001',100.00);
INSERT INTO test(Date_Year,Data_Month,Data_Day,Tjzb_bh,zbsj_data) VALUES('2010','05','02','0001',110.00);
INSERT INTO test(Date_Year,Data_Month,Data_Day,Tjzb_bh,zbsj_data) VALUES('2010','05','09','0001',120.00);
INSERT INTO test(Date_Year,Data_Month,Data_Day,Tjzb_bh,zbsj_data) VALUES('2010','05','31','0001',130.00);
INSERT INTO test(Date_Year,Data_Month,Data_Day,Tjzb_bh,zbsj_data) VALUES('2010','05','03','0002',60.00);
INSERT INTO test(Date_Year,Data_Month,Data_Day,Tjzb_bh,zbsj_data) VALUES('2010','05','10','0002',85.00);
INSERT INTO test(Date_Year,Data_Month,Data_Day,Tjzb_bh,zbsj_data) VALUES('2010','05','20','0002',90.00);COMMIT;-------------------------------------- 查询数据：---------------------------------------------------------
SELECT to_char(t1.perDay,'YYYY-MM-DD') perDay, t2.Tjzb_bh, nvl(t3.zbsj_data,00.00) as zbtj_data, SUM(nvl(t4.zbsj_data,0)) Total
FROM (
SELECT to_date('2010-05-01','YYYY-MM-DD') + level - 1 AS perDay FROM dual
CONNECT BY level <=
( last_day(to_date('2010-05-01','YYYY-MM-DD')) - to_date('2010-05-01','YYYY-MM-DD') + 1)
) t1 FULL JOIN (SELECT DISTINCT Tjzb_bh FROM test) t2 ON 1=1
LEFT JOIN test t3 ON t3.Date_Year||'-'||t3.Data_Month||'-'||t3.Data_Day = to_char(t1.perDay,'YYYY-MM-DD') AND t3.TjZb_bh = t2.Tjzb_bh
LEFT JOIN test t4 ON t4.Date_Year||'-'||t4.Data_Month||'-'||t4.Data_Day <= to_char(t1.perDay,'YYYY-MM-DD') AND t4.Tjzb_bh = t2.Tjzb_bh
GROUP BY to_char(t1.perDay,'YYYY-MM-DD'), t2.Tjzb_bh, nvl(t3.zbsj_data,00.00)
ORDER BY t2.Tjzb_bh, perDay;-------------------------------------- 查询结果：---------------------------------------------------------
PERDAY TJZB_BH ZBTJ_DATA TOTAL
-------------------- -------- ---------- ----------
2010-05-01 0001 100 100
2010-05-02 0001 110 210
2010-05-03 0001 0 210
2010-05-04 0001 0 210
2010-05-05 0001 0 210
2010-05-06 0001 0 210
2010-05-07 0001 0 210
2010-05-08 0001 0 210
2010-05-09 0001 120 330
2010-05-10 0001 0 330
2010-05-11 0001 0 330
2010-05-12 0001 0 330
2010-05-13 0001 0 330
2010-05-14 0001 0 330
2010-05-15 0001 0 330
2010-05-16 0001 0 330
2010-05-17 0001 0 330
2010-05-18 0001 0 330
2010-05-19 0001 0 330
2010-05-20 0001 0 330
2010-05-21 0001 0 330
2010-05-22 0001 0 330
2010-05-23 0001 0 330
2010-05-24 0001 0 330
2010-05-25 0001 0 330
2010-05-26 0001 0 330
2010-05-27 0001 0 330
2010-05-28 0001 0 330
2010-05-29 0001 0 330
2010-05-30 0001 0 330
2010-05-31 0001 130 460
2010-05-01 0002 0 0
2010-05-02 0002 0 0
2010-05-03 0002 60 60
2010-05-04 0002 0 60
2010-05-05 0002 0 60
2010-05-06 0002 0 60
2010-05-07 0002 0 60
2010-05-08 0002 0 60
2010-05-09 0002 0 60
2010-05-10 0002 85 145
2010-05-11 0002 0 145
2010-05-12 0002 0 145
2010-05-13 0002 0 145
2010-05-14 0002 0 145
2010-05-15 0002 0 145
2010-05-16 0002 0 145
2010-05-17 0002 0 145
2010-05-18 0002 0 145
2010-05-19 0002 0 145
2010-05-20 0002 90 235
2010-05-21 0002 0 235
2010-05-22 0002 0 235
2010-05-23 0002 0 235
2010-05-24 0002 0 235
2010-05-25 0002 0 235
2010-05-26 0002 0 235
2010-05-27 0002 0 235
2010-05-28 0002 0 235
2010-05-29 0002 0 235
2010-05-30 0002 0 235
2010-05-31 0002 0 235已选择62行。已用时间: 00: 00: 00.14
SELECT ROW_NUMBER() OVER(ORDER BY m.Tjzb_bh, m.perDay) as ID,
m.perDay, m.Tjzb_bh, m.zbtj_data, m.Total
FROM (
SELECT to_char(t1.perDay,'YYYY-MM-DD') perDay, t2.Tjzb_bh, nvl(t3.zbsj_data,00.00) as zbtj_data, SUM(nvl(t4.zbsj_data,0)) Total
FROM (
SELECT to_date('2010-05-01','YYYY-MM-DD') + level - 1 AS perDay FROM dual
CONNECT BY level <=
( last_day(to_date('2010-05-01','YYYY-MM-DD')) - to_date('2010-05-01','YYYY-MM-DD') + 1)
) t1 FULL JOIN (SELECT DISTINCT Tjzb_bh FROM test) t2 ON 1=1
LEFT JOIN test t3 ON t3.Date_Year||'-'||t3.Data_Month||'-'||t3.Data_Day = to_char(t1.perDay,'YYYY-MM-DD') AND t3.TjZb_bh = t2.Tjzb_bh
LEFT JOIN test t4 ON t4.Date_Year||'-'||t4.Data_Month||'-'||t4.Data_Day <= to_char(t1.perDay,'YYYY-MM-DD') AND t4.Tjzb_bh = t2.Tjzb_bh
GROUP BY to_char(t1.perDay,'YYYY-MM-DD'), t2.Tjzb_bh, nvl(t3.zbsj_data,00.00)
) m;
-------------------------------------- 查询结果：--------------------------------------------------------- ID PERDAY TJZB_BH ZBTJ_DATA TOTAL
---------- -------------------- -------- ---------- ----------
1 2010-05-01 0001 100 100
2 2010-05-02 0001 110 210
3 2010-05-03 0001 0 210
4 2010-05-04 0001 0 210
5 2010-05-05 0001 0 210
6 2010-05-06 0001 0 210
7 2010-05-07 0001 0 210
8 2010-05-08 0001 0 210
9 2010-05-09 0001 120 330
10 2010-05-10 0001 0 330
11 2010-05-11 0001 0 330
12 2010-05-12 0001 0 330
13 2010-05-13 0001 0 330
14 2010-05-14 0001 0 330
15 2010-05-15 0001 0 330
16 2010-05-16 0001 0 330
17 2010-05-17 0001 0 330
18 2010-05-18 0001 0 330
19 2010-05-19 0001 0 330
20 2010-05-20 0001 0 330
21 2010-05-21 0001 0 330
22 2010-05-22 0001 0 330
23 2010-05-23 0001 0 330
24 2010-05-24 0001 0 330
25 2010-05-25 0001 0 330
26 2010-05-26 0001 0 330
27 2010-05-27 0001 0 330
28 2010-05-28 0001 0 330
29 2010-05-29 0001 0 330
30 2010-05-30 0001 0 330
31 2010-05-31 0001 130 460
32 2010-05-01 0002 0 0
33 2010-05-02 0002 0 0
34 2010-05-03 0002 60 60
35 2010-05-04 0002 0 60
36 2010-05-05 0002 0 60
37 2010-05-06 0002 0 60
38 2010-05-07 0002 0 60
39 2010-05-08 0002 0 60
40 2010-05-09 0002 0 60
41 2010-05-10 0002 85 145
42 2010-05-11 0002 0 145
43 2010-05-12 0002 0 145
44 2010-05-13 0002 0 145
45 2010-05-14 0002 0 145
46 2010-05-15 0002 0 145
47 2010-05-16 0002 0 145
48 2010-05-17 0002 0 145
49 2010-05-18 0002 0 145
50 2010-05-19 0002 0 145
51 2010-05-20 0002 90 235
52 2010-05-21 0002 0 235
53 2010-05-22 0002 0 235
54 2010-05-23 0002 0 235
55 2010-05-24 0002 0 235
56 2010-05-25 0002 0 235
57 2010-05-26 0002 0 235
58 2010-05-27 0002 0 235
59 2010-05-28 0002 0 235
60 2010-05-29 0002 0 235
61 2010-05-30 0002 0 235
62 2010-05-31 0002 0 235已选择62行。已用时间: 00: 00: 00.26
特别感谢 luoyoumou ，通过他的方法，得到了我想要的结果，不过有些地方还需要我好好琢磨一下，特别是对Oracle中的关键字的用法！看来我对plsql的了解还非常之浅薄！望能与各位成为好友，以后多多指教！！！