create table test1(mid varchar(10),mname varchar(20));--机房表
insert into test1 values('1','机房1');
insert into test1 values('2','机房2');
insert into test1 values('3','机房3');
insert into test1 values('4','机房4');
create table test2(gid varchar(10),gname varchar(20));--城市表
insert into test2 values('1','深圳');
insert into test2 values('2','武汉');
insert into test2 values('3','上海');
insert into test2 values('4','长沙');
create table test3(gid varchar(10),mid varchar(20));--前2项关联表
insert into test3 values('1','1');
insert into test3 values('1','2');
insert into test3 values('2','1');
insert into test3 values('2','2');
insert into test3 values('3','1');
insert into test3 values('3','2');
insert into test3 values('4','2');
insert into test3 values('4','3');
create table test4(mid varchar(10),istimeout varchar(20),createdate datetime);--业务1表，0表示未超时，1表示超时
insert into test4 values('1','0','2014-07-16 10:00');
insert into test4 values('1','1','2014-07-16 10:00');
insert into test4 values('1','0','2014-07-16 10:00');
insert into test4 values('1','1','2014-07-17 10:00');
insert into test4 values('2','0','2014-07-18 10:00');
insert into test4 values('2','1','2014-07-19 10:00');
insert into test4 values('2','0','2014-07-20 10:00');
insert into test4 values('2','1','2014-07-21 10:00');
insert into test4 values('3','0','2014-07-20 10:00');
insert into test4 values('3','1','2014-07-18 10:00');
insert into test4 values('4','0','2014-07-17 10:00');
insert into test4 values('4','1','2014-07-16 10:00');
create table test5(mid varchar(10),istimeout varchar(20),createdate datetime);--业务2表，0表示未超时，1表示超时
insert into test5 values('1','0','2014-07-16 10:00');
insert into test5 values('1','1','2014-07-16 10:00');
insert into test5 values('1','0','2014-07-16 10:00');
insert into test5 values('1','1','2014-07-17 10:00');
insert into test5 values('2','0','2014-07-18 10:00');
insert into test5 values('2','1','2014-07-19 10:00');
insert into test5 values('2','0','2014-07-20 10:00');
insert into test5 values('2','1','2014-07-21 10:00');
insert into test5 values('3','0','2014-07-20 10:00');
insert into test5 values('3','1','2014-07-18 10:00');
insert into test5 values('4','0','2014-07-17 10:00');
insert into test5 values('4','1','2014-07-16 10:00');
城市 机房 业务1已超时数量 业务1未超时数量 业务2已超时数量 业务2未超时数量 业务1已超时数量合计 业务1未超时数量合计 业务2已超时数量合计 业务2未超时数量合计
上面是要显示的横向字段，还需要显示纵向这和，列都是固定的。传入的条件是时间段，后面这4项合计主要是为当查询范围为时间段时，可以自动统计之前时间的范围之和
-------------------------------------------------------------------------------------------------
select b.gName, a.mname,
sum(case when d.isTimeout=0 then 1 else 0 end) as '宽带未超时',
sum(case when d.isTimeout=1 then 1 else 0 end) as '宽带己超时',
sum(case when e.isTimeout=0 then 1 else 0 end) as '固话未超时',
sum(case when e.isTimeout=1 then 1 else 0 end) as '固话己超时',
sum(case when d.isTimeout=0 then 1 else 0 end) AS '宽带未超时合计',
sum(case when d.isTimeout=1 then 1 else 0 end) as '宽带己超时合计',
sum(case when e.isTimeout=0 then 1 else 0 end) AS '固话未超时合计',
sum(case when e.isTimeout=1 then 1 else 0 end) as '固话己超时合计'
from
dbo.test1 a,
dbo.test2 b,
dbo.test3 c,
dbo.test4 d,
dbo.test5 e
where a.mid=c.mid and b.gid=c.gid
and d.mid=a.mid and e.mid=a.mid
group by b.gName, a.mname
这是我写的，但是不知道怎么横向和纵向求和，以及怎么传入时间再得出之前时间段这和。
------解决方案--------------------
declare @sdate datetime
declare @edate datetime
set @sdate='2014-7-18'
set @edate='2014-7-20'
;with testa as (
select a.*,b.mname,c.gname,d.istimeout,d.createdate,case when istimeout=1 then 'a1' else 'a0' end as [type]
from test3 a
left join test1 b on a.mid=b.mid
left join test2 c on a.gid=c.gid
left join test4 d on a.mid=d.mid
union all
select a.*,b.mname,c.gname,d.istimeout,d.createdate,case when istimeout=1 then 'b1' else 'b0' end as [type]
from test3 a
left join test1 b on a.mid=b.mid
left join test2 c on a.gid=c.gid
left join test5 d on a.mid=d.mid
),
testb as (
select a.*,b.mname,c.gname,d.istimeout,d.createdate,case when istimeout=1 then 'a1' else 'a0' end as [type]