現有兩表
表A : 表B :
UserID UKey FKey
1 A A
1 B B
2 A C
2 B
2 C
3 A
3 E
3 C
現需要求出表A中的Ukey全部存在于表B中的Fkey(明確多過FKey也不行)的所有UserID
如上得出結果:
UserID IsExist
1 No
2 Yes
3 No
謝謝!
------解决方案--------------------
create table A(id int,UKey nvarchar(10))
insert into A select 1, 'A '
insert into A select 1, 'B '
insert into A select 2, 'A '
insert into A select 2, 'B '
insert into A select 2, 'C '
insert into A select 3, 'A '
insert into A select 3, 'E '
insert into A select 3, 'C '
create table B(FKey nvarchar(10))
insert into B select 'A '
insert into B select 'B '
insert into B select 'C '
select id,case _count when (select count(*) from b) then 'yes ' else 'no ' end from
(select id,count(*) _count from a,b where a.UKey = b.FKey group by id) as a
------解决方案--------------------
SELECT DISTINCT a.UserID, CASE WHEN b.FKey IS NULL THEN 'No ' ELSE 'Yes ' END AS IsExist
FROM a
LEFT JOIN b ON a.UKey = b.FKey
ORDER BY a.UserID
------解决方案--------------------