请帮忙写个简单的语句。
两个表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
[解决办法]
select A.*,case when exists(select 1 from B where B.id=A.id) then 1 else 0 end
from A
[解决办法]
select distinct a.id,a.a1,a.a2,(case when b.id is not null then 1 else 0 end ) asc
from a left outer join b on a.id=b.id
[解决办法]
create table A(id int,a1 varchar(10),a2 varchar(10))
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 A
id a1 a2
----------- ---------- ---------- -----------
1 a a 0
2 b b 1
3 c c 0
(所影响的行数为 3 行)
[解决办法]
SELECT
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