这是我的写法,但是感觉不太简洁 select a.xx, a.y, b.y
from
(select table1.xx, sum(table2.y) y
from table1 left join table2 on table1.x=table2.x
group by table1.xx) as a , (select table1.xx, sum(table3.y) y
from table1 left join table3 on table1.x=table3.x
group by table1.xx) as b where a.xx=b.xx and (a.y<>0 or b.y<>0)
from
(select table1.xx, sum(table2.y) y
from table1 left join table2 on table1.x=table2.x
group by table1.xx) as a , (select table1.xx, sum(table3.y) y
from table1 left join table3 on table1.x=table3.x
group by table1.xx) as b where a.xx=b.xx and (a.y<>0 or b.y<>0)
sum(isnull(aa.by,0)) as by,
sum(isnull(aa.cy,0)) as cy
from (select a.xx,b.y as by,c.y as cy
from t1 a left join t2 b on a.x=b.x
left join t3 c on a.x=c.x ) aa
group by aa.xx
sum(isnull(aa.by,0)) as by,
sum(isnull(aa.cy,0)) as cy
from (select a.xx,b.y as by,c.y as cy
from t1 a left join t2 b on a.x=b.x
left join t3 c on a.x=c.x
where b.y is not null and c.y is not null --加条件试试
) aa
group by aa.xx
select a.xx,sum(isnull(aa.by,0)) as by,sum(isnull(aa.cy,0)) as cy
from table1 a left join table2 b on a.x=b.x
left join table3 c on a.x=c.x
where b.y is not null or c.y is not null //这里是or而不是and
group by a.xx
group by c.xx
group by c.x
group by c.x