问一句简单的SQL

[bigmingming]

表A
ID NAME 求是否出现
1 AA 是
2 BB 是
3 CC 否

表B
ID NOTE
1 ABC
1 ABC
2 ABC
2 ABC

求表A中的ID是否在表B中出现过

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([ID] int,[NAME] nvarchar(22))
Insert #A
select 1,N'AA' union all
select 2,N'BB' union all
select 3,N'CC'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([ID] int,[NOTE] nvarchar(23))
Insert #B
select 1,N'ABC' union all
select 1,N'ABC' union all
select 2,N'ABC' union all
select 2,N'ABC'
Go
--测试数据结束
SELECT  #A.ID ,
        NAME ,
        CASE WHEN MAX(#B.ID) IS NOT NULL THEN '是'
             ELSE '否'
        END AS 是否出现
FROM    #A
        LEFT JOIN #B ON #B.ID = #A.ID
GROUP BY #A.ID ,
        NAME

[繁花尽流年]

select id from a where exists (select 1 from b where id=a.id) select id from a where not exists (select 1 from b where id=a.id)

[听雨停了]

引用 楼主 bigmingming 的回复:

表A ID NAME 求是否出现 1 AA 是 2 BB 是 3 CC 否 表B ID NOTE 1 ABC 1 ABC 2 ABC 2 ABC 求表A中的ID是否在表B中出现过

这么简单的sql你也来这问？自己不会百度啊，得学会自学，自学懂吗？

[喜-喜]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([ID] int,[NAME] nvarchar(22))
Insert #A
select 1,N'AA' union all
select 2,N'BB' union all
select 3,N'CC'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([ID] int,[NOTE] nvarchar(23))
Insert #B
select 1,N'ABC' union all
select 1,N'ABC' union all
select 2,N'ABC' union all
select 2,N'ABC'
Go
--测试数据结束
SELECT  #A.ID ,
        NAME ,
        CASE WHEN MAX(#B.ID) IS NOT NULL THEN '是'
             ELSE '否'
        END AS 是否出现
FROM    #A
        LEFT JOIN #B ON #B.ID = #A.ID
GROUP BY #A.ID ,
        NAME

与 exists 相比较，更推荐使用 left join！

[中国风]

e.g.

SELECT  A.* ,
        CASE WHEN B.ID IS NOT NULL THEN '是'
             ELSE '否'
        END AS 求是否出现
FROM    A
        LEFT JOIN ( SELECT DISTINCT
                            ID
                    FROM    B
                  ) AS B ON A.ID = B.ID