用SQL做查询,两个表,ID关联,A表很全,B表有的没有,怎么让B表没有的都显示为0?
比如A表:ID:001,002,003
B表ID:002,
AMOUNT:100我想查出这样的效果
ID AMOUNT
001 0
002 100
003 0
用SQL怎么写?
比如A表:ID:001,002,003
B表ID:002,
AMOUNT:100我想查出这样的效果
ID AMOUNT
001 0
002 100
003 0
用SQL怎么写?
from A
left join B
on A.id=B.id
select a.id,isnull(amount,0) amount from a left join b on a.id=b.id
create table A(ID int)
insert A select 001 union all select 002 union all select 003
create table B(ID int,AMOUNT int)
insert B select 002,100
select A.ID , isnull(B.AMOUNT,0) as AMOUNT from A left join B on A.ID=B.ID