請教一個復雜的比較問題!
現有兩表
表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
------解决方案--------------------
create table a(userid int,Ukey varchar(10))
insert a
select 1 , 'A '
union all select 1, 'B '
union all select 1, 'D '
union all select 2, 'A '
union all select 2, 'B '
union all select 2, 'C '
union all select 3, 'B '
union all select 3, 'C '
union all select 3, 'D '
union all select 3, 'E '
create table b(FKey char(1))
insert b
select 'A '
union select 'B '
union select 'C '
go
create function fff(@id int)
returns varchar(100)
as
begin
declare @str varchar(100) , @result varchar(10)
declare @str1 varchar(10)
set @str= ' '
set @str1= ' '
set @result= ' '
select @str1=@str1+FKey from b
select @str=@str+Ukey from a where userid=@id
if (@str1=@str)
set @result= 'True '
else
set @result= 'False '
return @result
end
go
select dbo.fff(userid)
from a
group by userid having count(1)> =3
go
drop function fff
drop table a,b
