两个表A,B
A中字段id,a1,a2,
B中字段id,b1,b2
需要实现选择A表中所有记录,通过ID在B表中查找,如果有记录则状态显示1,没有记录显示0
A表
1,a,a
2,b,b
3,c,c
B表
2,e,e
查询数据结果
1,a,a,0
2,b,b,1
3,c,c,0
A中字段id,a1,a2,
B中字段id,b1,b2
需要实现选择A表中所有记录,通过ID在B表中查找,如果有记录则状态显示1,没有记录显示0
A表
1,a,a
2,b,b
3,c,c
B表
2,e,e
查询数据结果
1,a,a,0
2,b,b,1
3,c,c,0
from A
from a left outer join b on a.id=b.id
insert A select 1,'a','a'
union all select 2,'b','b'
union all select 3,'c','c'create table B(id int,b1 varchar(10),b2 varchar(10))
insert B select 2,'e','e'select A.*,case when exists(select 1 from B where B.id=A.id) then 1 else 0 end
from Aid a1 a2
----------- ---------- ---------- -----------
1 a a 0
2 b b 1
3 c c 0(所影响的行数为 3 行)
A.id,
A.a1,
A.a2,
CASE A.id
WHEN B.id THEN 1 ELSE 0 END AS Status
FROM
A LEFT JOIN B ON A.id = B.id
select distinct a.id,a.a1,a.a2,(case when b.id is not null then 1 else 0 end ) as c
from a left outer join b on a.id=b.id
insert @t1 select 1,'a','a'
union all select 2,'b','b'
union all select 3,'c','c'declare @t2 table(id int,b1 varchar(10),b2 varchar(10))
insert @t2 select 2,'e','e'select a.*,state=case when b.id is null then 0 else 1 end
from @t1 a left join @t2 b on a.id=b.id
/*
查询数据结果
1,a,a,0
2,b,b,1
3,c,c,0
*/