select new.flot,new.fop,sum(isnull(Fqty,0)) as Fqty
from (select new1.flot,new2.fop
from (select distinct flot from D) new1,
(select b.flot,c.fop from B b,C c where b.froute=c.froute) new2
where new1.flot=new2.flot
) new left join
A a on new.flot=a.flot and new.fop=a.fop
group by new.flot,new.fop
order by new.flot,new.fop
from (select new1.flot,new2.fop
from (select distinct flot from D) new1,
(select b.flot,c.fop from B b,C c where b.froute=c.froute) new2
where new1.flot=new2.flot
) new left join
A a on new.flot=a.flot and new.fop=a.fop
group by new.flot,new.fop
order by new.flot,new.fop
,T2.Fop
,sum(Fqty) as 'Fqty'
from (
select distinct Flot from D
)T1
left join (
select distinct Fop from C
)T2 on 1>0left join A on T1.Flot=A.Flot and T2.Fop=A.Fop
group by T1.Flot,T2.Fop
declare @A table(Fid varchar(20),Flot varchar(20),Fop varchar(20),Fqty int)
declare @B table(Flot varchar(20),Froute varchar(20))
declare @c table(Froute varchar(20),Fop varchar(20))
declare @d table(Flot varchar(20),Fop varchar(20))
insert into @A
select 'A01', 'test01', 'op-1', 3 union
select 'A02', 'test01', 'op-1', 1 union
select 'A03', 'test01', 'op-2', 3 union
select 'A05', 'test02', 'op-1', 1insert into @B
select 'test01', 'route1' union
select 'test02', 'route1' union
select 'test03', 'route1' union
select 'test04', 'route1'insert into @C
select 'route1', 'op-1' union
select 'route1', 'op-2' union
select 'route1', 'op-3' union
select 'route1', 'op-4'insert into @D
select 'test01', 'op-1' union
select 'test01', 'op-2' union
select 'test02', 'op-1' union
select 'test03', 'op-2'
--语句
select new.flot,new.fop,sum(isnull(Fqty,0)) as Fqty
from (select new1.flot,new2.fop
from (select distinct flot from @D) new1,
(select b.flot,c.fop from @B b,@C c where b.froute=c.froute) new2
where new1.flot=new2.flot
) new left join
@A a on new.flot=a.flot and new.fop=a.fop
group by new.flot,new.fop
order by new.flot,new.fop
--结果
/*
test01 op-1 4
test01 op-2 3
test01 op-3 0
test01 op-4 0
test02 op-1 1
test02 op-2 0
test02 op-3 0
test02 op-4 0
test03 op-1 0
test03 op-2 0
test03 op-3 0
test03 op-4 0*/