17,382
社区成员




--生成测试数据
create table w_testA(
id number,
name varchar2(20),
did number
);
insert into w_testA values(101,'test1',1);
insert into w_testA values(102,'test2',2);
insert into w_testA values(103,'test3',1);
create table w_testB(
aid number,
"date" varchar2(10),
month number,
value number
);
insert into w_testB values(101,'2011-1-1',1,111 );
insert into w_testB values(101,'2011-2-1',2,222 );
insert into w_testB values(102,'2011-1-1',1,1212);
insert into w_testB values(102,'2011-2-1',2,2121);
insert into w_testB values(103,'2011-1-1',1,1313);
insert into w_testB values(103,'2011-2-1',2,2323);
commit;
--开始查询
select a.id,a.name,t.dd,t.mm,nvl(b.value,0) value from (
select rownum mm,'2011-'||rownum||'-1' dd from user_objects where rownum<=5
) t cross join w_testA a
left join w_testB b on (t.mm=b.month and a.id=b.aid)
where a.did=1
order by a.id,t.mm;
/*
ID NAME DD MM VALUE
101 test1 2011-1-1 1 111
101 test1 2011-2-1 2 222
101 test1 2011-3-1 3 0
101 test1 2011-4-1 4 0
101 test1 2011-5-1 5 0
103 test3 2011-1-1 1 1313
103 test3 2011-2-1 2 2323
103 test3 2011-3-1 3 0
103 test3 2011-4-1 4 0
103 test3 2011-5-1 5 0
*/
--结束查询
drop table w_testA;
drop table w_testB;
[Quote=引用 1 楼 geniuswjt 的回复:]