三个表分别为fl(分类),dw(单位),allxx(所有信息)
create table fl(flid varchar2(4) not null,flm varchar2(20))
create table dw(dwid varchar2(4) not null,dwmc varchar2(20))
create table allxx(dwid varchar2(4) not null,flid varchar2(4),rq date)insert into fl values('1','分类1');insert into fl values('2','分类2');insert into fl values('3','分类3')insert into dw values('a','单位1');insert into dw values('b','单位2');insert into dw values('c','单位3')insert into allxx values('a','1',TO_DATE('2005-01-01','YYYY-MM-DD'))
insert into allxx values('a','1',TO_DATE('2005-02-01','YYYY-MM-DD'))
insert into allxx values('a','2',TO_DATE('2005-03-01','YYYY-MM-DD'))
insert into allxx values('b','2',TO_DATE('2005-04-01','YYYY-MM-DD'))
insert into allxx values('b','3',TO_DATE('2005-05-01','YYYY-MM-DD'))
现在我想查询3各单位每个分类信息统计,我看了前辈们的帖子,自解决了一半,
select a.dwid,b.flid,count(b.flid) as total from dw a,allxx b where a.dwid=b.dwid(+) group by a.dwid,b.flid
dwid flid total
---------------------
a 1 2
a 2 1
b 2 1
b 3 1
c 0
-----------------------
可我的本意是:每个单位,每一分类都统计,没有的添零,但我只会用oracle两个表外联怎么实现下面的结果,请指点,谢谢!
dwid flid total
---------------------
a 1 2
a 2 1
a 3 0
b 2 1
b 3 1
c 1 0
c 2 0
c 3 0
-----------------------
如果按月份呢?
rq flid total
--------------------------
1月 1 ?
1月 2 ?
1月 3 ?
2月 1 ?
2月 2 ?
2月 3 ?
......
12月 1 ?
12月 2 ?
12月 3 ?
还是一样的问题,如果某月某一个分类没有添零!
create table fl(flid varchar2(4) not null,flm varchar2(20))
create table dw(dwid varchar2(4) not null,dwmc varchar2(20))
create table allxx(dwid varchar2(4) not null,flid varchar2(4),rq date)insert into fl values('1','分类1');insert into fl values('2','分类2');insert into fl values('3','分类3')insert into dw values('a','单位1');insert into dw values('b','单位2');insert into dw values('c','单位3')insert into allxx values('a','1',TO_DATE('2005-01-01','YYYY-MM-DD'))
insert into allxx values('a','1',TO_DATE('2005-02-01','YYYY-MM-DD'))
insert into allxx values('a','2',TO_DATE('2005-03-01','YYYY-MM-DD'))
insert into allxx values('b','2',TO_DATE('2005-04-01','YYYY-MM-DD'))
insert into allxx values('b','3',TO_DATE('2005-05-01','YYYY-MM-DD'))
现在我想查询3各单位每个分类信息统计,我看了前辈们的帖子,自解决了一半,
select a.dwid,b.flid,count(b.flid) as total from dw a,allxx b where a.dwid=b.dwid(+) group by a.dwid,b.flid
dwid flid total
---------------------
a 1 2
a 2 1
b 2 1
b 3 1
c 0
-----------------------
可我的本意是:每个单位,每一分类都统计,没有的添零,但我只会用oracle两个表外联怎么实现下面的结果,请指点,谢谢!
dwid flid total
---------------------
a 1 2
a 2 1
a 3 0
b 2 1
b 3 1
c 1 0
c 2 0
c 3 0
-----------------------
如果按月份呢?
rq flid total
--------------------------
1月 1 ?
1月 2 ?
1月 3 ?
2月 1 ?
2月 2 ?
2月 3 ?
......
12月 1 ?
12月 2 ?
12月 3 ?
还是一样的问题,如果某月某一个分类没有添零!
select *
from
(
select 1 as rq, 1 as flid from dual
union all
select 1, 2 from dual
union all
select 1, 3 from dual
...
select 12, 1 from dual
union all
select 12, 2 from dual
union all
select 12, 3 from dual
) R1,
f1
where R1.flid = f1.flid(+)
...
group by ...
---- --------------------
a 单位1
b 单位2
c 单位3SQL> select * From fl;FLID FLM
---- --------------------
1 分类1
2 分类2
3 分类3SQL> select * from allxx;DWID FLID RQ
---- ---- --------------
a 1 01-1月 -05
a 1 01-2月 -05
a 2 01-3月 -05
b 2 01-4月 -05
b 3 01-5月 -05SQL> select dwid,flid,sum(count_flid) from (
2 select a.dwid,b.flid,0 count_flid from dw a,fl b
3 union all
4 select dwid,flid,count(flid) count_flid from allxx group by dwid,flid)
5 group by dwid,flid
6 order by dwid,flid
7 /DWID FLID SUM(COUNT_FLID)
---- ---- ---------------
a 1 2
a 2 1
a 3 0
b 1 0
b 2 1
b 3 1
c 1 0
c 2 0
c 3 0已选择9行。