需求:
表: tab1, tab2, tab3, tab4(这四张表没有任何关联,并且字段名称也一样,但里面都包含一个日期列)
要求 : 通过这四张表建立一个统计视图
如: select count(*) from tab* where 日期="***" 实现效果:年份 | 项目1(tab1) | 项目2(tab1)| 项目3(tab1)| 项目4 (tab1)
xxxx |1 |2 |3 |4
--1:
select count(*) from tab1 where 日期="xxxx"
--2:
select count(*) from tab2 where 日期="xxxx"
--3:
select count(*) from tab3 where 日期="xxxx"
--4:
select count(*) from tab2 where 日期="xxxx"
----------------------------------------------
create table tab1( tab1pk int identity(1,1), --主键
tab1count int, --随便的值
tab1date date --日期
)
go
create table tab2( tab2pk int identity(1,1), --主键
tab2count int, --随便的值
tab2date date --日期
)
go
create table tab3( tab3pk int identity(1,1), --主键
tab3count int, --随便的值
tab3date date --日期
)
go
create table tab4( tab4pk int identity(1,1), --主键
tab4count int, --随便的值
tab4date date --日期
)
goinsert into tab1 values(1,'2005-06-09')
insert into tab1 values(2,'2006-06-09')insert into tab2 values(1,'2005-06-09')
insert into tab2 values(2,'2006-06-09')insert into tab3 values(1,'2005-06-09')
insert into tab3 values(2,'2006-06-09')
insert into tab4 values(1,'2005-06-09')
insert into tab4 values(2,'2006-06-09')
根据年份统计表中数据,只通过一个视图实现
请教高手,我该如何做
表: tab1, tab2, tab3, tab4(这四张表没有任何关联,并且字段名称也一样,但里面都包含一个日期列)
要求 : 通过这四张表建立一个统计视图
如: select count(*) from tab* where 日期="***" 实现效果:年份 | 项目1(tab1) | 项目2(tab1)| 项目3(tab1)| 项目4 (tab1)
xxxx |1 |2 |3 |4
--1:
select count(*) from tab1 where 日期="xxxx"
--2:
select count(*) from tab2 where 日期="xxxx"
--3:
select count(*) from tab3 where 日期="xxxx"
--4:
select count(*) from tab2 where 日期="xxxx"
----------------------------------------------
create table tab1( tab1pk int identity(1,1), --主键
tab1count int, --随便的值
tab1date date --日期
)
go
create table tab2( tab2pk int identity(1,1), --主键
tab2count int, --随便的值
tab2date date --日期
)
go
create table tab3( tab3pk int identity(1,1), --主键
tab3count int, --随便的值
tab3date date --日期
)
go
create table tab4( tab4pk int identity(1,1), --主键
tab4count int, --随便的值
tab4date date --日期
)
goinsert into tab1 values(1,'2005-06-09')
insert into tab1 values(2,'2006-06-09')insert into tab2 values(1,'2005-06-09')
insert into tab2 values(2,'2006-06-09')insert into tab3 values(1,'2005-06-09')
insert into tab3 values(2,'2006-06-09')
insert into tab4 values(1,'2005-06-09')
insert into tab4 values(2,'2006-06-09')
根据年份统计表中数据,只通过一个视图实现
请教高手,我该如何做
select 日期, sum(项目一),sum(项目二),sum(项目三),sum(项目四)
from
(
select 日期, count(*) 项目一, 0, 0, 0 from tab1 group by 日期
union all
select 日期, 0, count(*) 项目二, 0, 0 from tab2 group by 日期
union all
select 日期, 0, 0, count(*) 项目三, 0 from tab3 group by 日期
union all
select 日期, 0, 0, 0, count(*)项目四 from tab4 group by 日期
)
group by 日期
select 日期,sum(给号函),sum(国籍证),sum(未登记函件),sum(临时国籍证)
from
(
select po.rq 日期,count(*) 给号函,0,0,0 from AW_GJZ_GHH po where po.deleted=0 group by po.rq
union all
select po.bfrq 日期,0,count(*) 国籍证,0,0 from AW_GJZ_ZJB po where po.deleted=0 group by po.bfrq
union all
select po.approvaldate 日期,0,0,count(*) 未登记函件,0 from AW_DEREGISTRATION po where po.isdeleted=0 group by po.approvaldate
union all
select po.bfrq 日期,0,0,0,count(*) 临时国籍证 from AW_GJZ_ZJB po where po.deleted=0 and po.zjbclx='20060113080130' group by po.bfrq
)
group by 日期
select 日期,sum(给号函),sum(国籍证),sum(未登记函件),sum(临时国籍证)
from
(
select po.rq 日期,count(*) 给号函,0 国籍证,0 未登记函件,0 临时国籍证 from AW_GJZ_GHH po where po.deleted=0 group by po.rq
union all
select po.bfrq 日期,0,count(*) 国籍证,0,0 from AW_GJZ_ZJB po where po.deleted=0 group by po.bfrq
union all
select po.approvaldate 日期,0,0,count(*) 未登记函件,0 from AW_DEREGISTRATION po where po.isdeleted=0 group by po.approvaldate
union all
select po.bfrq 日期,0,0,0,count(*) 临时国籍证 from AW_GJZ_ZJB po where po.deleted=0 and po.zjbclx='20060113080130' group by po.bfrq
)
group by 日期
select sum(cc.jj),sum(cc.gg),sum(cc.ww),sum(cc.ss) from
(
select dd.日期,sum(dd.给号函) jj,sum(dd.国籍证) gg,sum(dd.未登记函件) ww,sum(dd.临时国籍证) ss
from
(
select po.rq 日期,count(*) 给号函,0 国籍证,0 未登记函件,0 临时国籍证 from AW_GJZ_GHH po where po.deleted=0 group by po.rq
union all
select po.bfrq 日期,0,count(*) 国籍证,0,0 from AW_GJZ_ZJB po where po.deleted=0 group by po.bfrq
union all
select po.approvaldate 日期,0,0,count(*) 未登记函件,0 from AW_DEREGISTRATION po where po.isdeleted=0 group by po.approvaldate
union all
select po.bfrq 日期,0,0,0,count(*) 临时国籍证 from AW_GJZ_ZJB po where po.deleted=0 and po.zjbclx='20060113080130' group by po.bfrq
) dd
group by 日期) cc where cc.日期>TO_DATE('2004-12-31', 'YYYY-MM-DD') and cc.日期<=TO_DATE('2005-12-31', 'YYYY-MM-DD')