表1:t_department(营业厅表)
dept_id dept_name
01 1厅
02 2厅
。表2:t_bb_g(GSM)
dr(当日收入) rq dept_name type
100 2006-4-1 1厅 G
150 2006-4-1 2厅 G
表3:t_bb_c(CDMA)表结构与上相同。只是type字段改C
表4:t_bb_s(数据)表结构与上相同。只是type字段改S
表5:t_bb_h(互连网)表结构与上相同。只是type字段改H
表6:t_bb_ct(长途)表结构与上相同。只是type字段改CT现在报表格式为:营业厅名称 GSM CDMA 数据 互连网 长途 应收合计
------------------------------------------------------------------
按照日期段进行统计,并且把t_department表中所有的营业厅都列出来,如果某个营业厅没有收入
就把dr值设置为0.请问SQL语句该怎样写?谢谢。
dept_id dept_name
01 1厅
02 2厅
。表2:t_bb_g(GSM)
dr(当日收入) rq dept_name type
100 2006-4-1 1厅 G
150 2006-4-1 2厅 G
表3:t_bb_c(CDMA)表结构与上相同。只是type字段改C
表4:t_bb_s(数据)表结构与上相同。只是type字段改S
表5:t_bb_h(互连网)表结构与上相同。只是type字段改H
表6:t_bb_ct(长途)表结构与上相同。只是type字段改CT现在报表格式为:营业厅名称 GSM CDMA 数据 互连网 长途 应收合计
------------------------------------------------------------------
按照日期段进行统计,并且把t_department表中所有的营业厅都列出来,如果某个营业厅没有收入
就把dr值设置为0.请问SQL语句该怎样写?谢谢。
isnull(g.dr,0) as GSM,
isnull(c.dr,0) as CDMA,
... --其它类似
isnull(g.dr,0)+isnull(c.dr,0)+... as 应收合计
from t_department d
left join (
select dept_name,sum(dr) as dr
from t_bb_g
where rq between 查询日期范围
group by dept_name ) as g
on d.dept_name=g.dept_name
left join (
select dept_name,sum(dr) as dr
from t_bb_c
where rq between 查询日期范围
group by dept_name ) as c
on d.dept_name=c.dept_name
... --其它表类似
insert t_department
select '01','1厅' union
select '02','2厅'create table t_bb_g(dr money,rq datetime,dept_name varchar(20),type varchar(10))
insert t_bb_g
select 100,'2006-4-1','1厅','G' union
select 150,'2006-4-1','2厅','G'create table t_bb_c(dr money,rq datetime,dept_name varchar(20),type varchar(10))
create table t_bb_s(dr money,rq datetime,dept_name varchar(20),type varchar(10))
create table t_bb_h(dr money,rq datetime,dept_name varchar(20),type varchar(10))
create table t_bb_ct(dr money,rq datetime,dept_name varchar(20),type varchar(10))declare @begrq datetime
declare @endrq datetime
set @begrq='2006-4-1'
set @endrq='2006-4-1'select t_department.dept_name,
GSM=sum(case when g.dr is not null then g.dr else 0 end),
CDMA=sum(case when c.dr is not null then c.dr else 0 end),
数据=sum(case when s.dr is not null then s.dr else 0 end),
互连网=sum(case when h.dr is not null then h.dr else 0 end),
长途=sum(case when ct.dr is not null then ct.dr else 0 end),
应收合计=sum(case when g.dr is not null then g.dr else 0 end+
case when c.dr is not null then c.dr else 0 end+
case when s.dr is not null then s.dr else 0 end+
case when h.dr is not null then h.dr else 0 end+
case when ct.dr is not null then ct.dr else 0 end)
from t_department left join
(select dr,dept_name from t_bb_g where rq between @begrq and @endrq) g
on t_department.dept_name=g.dept_name
left join
(select dr,dept_name from t_bb_c where rq between @begrq and @endrq) c
on t_department.dept_name=c.dept_name
left join
(select dr,dept_name from t_bb_s where rq between @begrq and @endrq) s
on t_department.dept_name=s.dept_name
left join
(select dr,dept_name from t_bb_h where rq between @begrq and @endrq) h
on t_department.dept_name=h.dept_name
left join
(select dr,dept_name from t_bb_ct where rq between @begrq and @endrq) ct
on t_department.dept_name=ct.dept_name
group by t_department.dept_name