现在有一组数据,
例如:
No. Month Money
1 1 100
1 3 200
2 3 100
2 4 500类似于这样的数据,但量要大很多。请问,应该如何写SQL语句,才能得到这样的结果,要12个月的数据都有。
No. Month Money
1 1 100
1 2
1 3 200
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 11
1 12
2 1
2 2
2 3 100
2 4 500
2 5
2 6
2 7
2 8
2 9
2 10
2 11
2 12 可以使用标准的SQL语句,也可以使用oracle提供的函数。但要是存储过程什么的,就不需要啦!
谁要是解决过这样的问题,就请教教我吧,谢谢啦!
例如:
No. Month Money
1 1 100
1 3 200
2 3 100
2 4 500类似于这样的数据,但量要大很多。请问,应该如何写SQL语句,才能得到这样的结果,要12个月的数据都有。
No. Month Money
1 1 100
1 2
1 3 200
1 4
1 5
1 6
1 7
1 8
1 9
1 10
1 11
1 12
2 1
2 2
2 3 100
2 4 500
2 5
2 6
2 7
2 8
2 9
2 10
2 11
2 12 可以使用标准的SQL语句,也可以使用oracle提供的函数。但要是存储过程什么的,就不需要啦!
谁要是解决过这样的问题,就请教教我吧,谢谢啦!
from
( select t2.m,t1.a,t1.mon
from (
select '01' m ,'a' a,100 mon from dual
union
select '01' m ,'b',200 from dual
) t1,
(
select to_char(add_months(to_date('2009-01-01', 'yyyy-mm-dd'), rownum - 1),
'mm') m
from all_objects
where rownum <= Months_between('2009-12-01', '2009-01-01') + 1
) t2
)t,
(
select '01' m ,'a' a,100 mon from dual
union
select '01' m ,'b',200 from dual
) wwhere w.m(+) = t.m
and w.a(+) = t.a
group by t.m, t.a, w.mon
order by t.a,t.m
SELECT A.NO,B.ID AS MONTH,A.MONEY
FROM A,
(SELECT ROWNUM AS ID FROM TABLENAME WHERE ROWNUM < 13) B
WHERE A.MONTH(+) = B.ID
------- 测试表
CREATE TABLE TEST_T
(
NO NUMBER,
MONTH VARCHAR2(2),
MONEY NUMBER);---- 测试数据
INSERT INTO TEST_T VALUES(1 ,'01', 100);
INSERT INTO TEST_T VALUES(1 ,'03', 200);
INSERT INTO TEST_T VALUES(2 ,'03', 100);
INSERT INTO TEST_T VALUES(2 ,'04', 500);
INSERT INTO TEST_T VALUES(3 ,'06', 100);
INSERT INTO TEST_T VALUES(6 ,'03', 999);
COMMIT;----- 查询语句
SELECT R1.NO,
R1.MONTH,
R2.MONEY
FROM (SELECT T1.NO,
T2.MONTH --- 生成每个NO对应12个月的字典表
FROM (SELECT DISTINCT NO
FROM TEST_T) T1, ----t1查询NO的数理
(SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2009-01-01',
'yyyy-mm-dd'),
ROWNUM - 1),
'MM') MONTH
FROM ALL_OBJECTS
WHERE ROWNUM <=
MONTHS_BETWEEN(TO_DATE('2009-12-01',
'YYYY-MM-DD'),
TO_DATE('2009-01-01',
'YYYY-MM-DD')) + 1) T2 ---- T2生成1-12个月
) R1 ---- 维表
LEFT OUTER JOIN TEST_T R2 --- 事实表
ON R1.NO = R2.NO
AND R1.MONTH = R2.MONTH
ORDER BY R1.NO,
R1.MONTH
----- 查询结果
NO MONTH MONEY
1 01 100
1 02
1 03 200
1 04
1 05
1 06
1 07
1 08
1 09
1 10
1 11
1 12
2 01
2 02
2 03 100
2 04 500
2 05
2 06
2 07
2 08
2 09
2 10
2 11
2 12
3 01
3 02
3 03
3 04
3 05
3 06 100
3 07
3 08
3 09
3 10
3 11
3 12
6 01
6 02
6 03 999
6 04
6 05
6 06
6 07
6 08
6 09
6 10
6 11
6 12
select t3.No,t3.month,t4.Money
from table t4
(select t1.No,t2.month
from
(select distinct t.No from table t)t1,
(select rownum month from dual connect by rownum<=12)t2
) t3
where t4.No(+)=t3.No
and t4.month(+)=t3.month
其他的就先好说了