查询语句的问题？？？急急急急急急！！！！

[上官云峰]

a结构a1,a2,a3
b结构b1,b2,b3,b4,b5
我的sql语句（a记录为5条，b记录为2条）
select
a.a1,
sum(case when b.b2=1 then b.b3 else 0 end)
sum(case when b.b2=1 then b.b4 else 0 end )
from
a left join b on (a.a1=b.b1)
where
b.b5=100
group by
a.a1
问：
因为有条件所以得不出5条记录，但是我必须要得出5条记录,请问这样得sql怎么写

[daijingjie2002]

select
a.a1,
sum(case when b.b2=1 then b.b3 else 0 end)
sum(case when b.b2=1 then b.b4 else 0 end )
from
a left join b on (a.a1=b.b1)
where
b.b5=100

[daijingjie2002]

不好意思以上发错
select a.a1,ww.a2,ww.a3 from a left (select
a.a1,
sum(case when b.b2=1 then b.b3 else 0 end) as a2
sum(case when b.b2=1 then b.b4 else 0 end ) as a3
from
a left join b on (a.a1=b.b1)
where
b.b5=100) as ww on a.a1=ww.a1

[CatchWind]

到SQL SERVER版,有高手在

[xluzhong]

select
a.a1,
sum(case when b.b2=1 then b.b3 else 0 end) as a2,
sum(case when b.b2=1 then b.b4 else 0 end) as a3
from a
left join (select * from b where b5=100) t
on a.a1=t.b1
group by a.a1

[xluzhong]

select
a.a1,
sum(case when t.b2=1 then b.b3 else 0 end) as a2,
sum(case when t.b2=1 then b.b4 else 0 end) as a3
from a
left join (select * from b where b5=100) t
on a.a1=t.b1
group by a.a1

[lishengyu]

select
a.a1,
sum(case when b.b2=1 then b.b3 else 0 end)
sum(case when b.b2=1 then b.b4 else 0 end )
from
a left join b on a.a1=b.b1 and b.b5=100
group by
a.a1

[lishengyu]

select
a.a1,
sum(case when b.b2=1 then b.b3 else 0 end)
sum(case when b.b2=1 then b.b4 else 0 end )
from
a left join b on a.a1=b.b1 and b.b5=100 ----这里就可以了
group by
a.a1