SQL连表查询，当不存在时赋值为0

[飞舞的锄头]

有两个表结果如下

先要通过A.A=B.A1查询出A表中在B表中对应的全部数据，不存在的赋值为0 结果如下

求SQL该怎么写？

[--小F--]

select
   a.a,a.b,a.c,isnull(b.a1,0) as a1
from 
   a left join b on a.a=b.a1

[--小F--]

select
   a.a,a.b,a.c,isnull(b.a1,'0') as a1
from 
   a left join b on a.a=b.a1

你B表的A1是字符型的

[發糞塗牆]

B1,C1要处理吗？

[唐诗三百首]

select a.A,a.B,a.C,isnull(b.A1,0) 'A1'
 from A a
 left join (select distinct A1 from B) b on a.A=b.A1

[wylwyl1108]

用 case when then

[wylwyl1108]

补充下： 全连接来做。。。然后把null换成 0 用case when then

[直面人生]

select a.A,a.B,a.C,isnull(b.A1,0) 'A1' from A a left join (select distinct A1 from B) b on a.A=b.A1 还是用 isnull()方便阿..