select 姓名from A where id in(select 疾病from B where 疾病 in ('糖尿病','高血压'))
select 姓名from A where id in(select 疾病from B where 疾病 in ('糖尿病') and 疾病 <> '高血压')
select 姓名from A where id in(select 疾病from B where 疾病 not in ('糖尿病','高血压'))
if object_id('a')>0
drop table a
if object_id('b') >0
drop table b
create table a (id int,name varchar(20))
insert into a
select 1,'張XX'
union all
select 2,'李XX'
create table b(id int,jibin varchar(20))
insert into b
select 1,' 糖尿病'
union all
select 1, '高血壓'
union all
select 2, ' 高血壓'
go
alter function fun_str(@id int)
returns varchar(200)
begin
declare @str varchar(200)
set @str=''
select @str=@str+','+jibin from b where id=@id
set @str=stuff(@str,1,1,'')
return (@str)
end
go
select id,name,
case when charindex('糖尿病',dbo.fun_str(id))>0
and charindex('高血壓',dbo.fun_str(id))>0
then '既得糖尿病又得高血壓的人'
when charindex('糖尿病',dbo.fun_str(id))>0
and charindex('高血壓',dbo.fun_str(id))=0
then '得糖尿病沒有得高血壓的人'
when charindex('糖尿病',dbo.fun_str(id))=0
and charindex('高血壓',dbo.fun_str(id))>0
then '得糖尿病沒有得高血壓的人'
end as jibin
from a
/*
id name jibin
--------------------------------------------------------------
1 張XX 既得糖尿病又得高血壓的人
2 李XX 得糖尿病沒有得高血壓的人
*/
if object_id('a')>0
drop table a
if object_id('b') >0
drop table b
create table a (id int,name varchar(20))
insert into a
select 1,'張XX'
union all
select 2,'李XX'
create table b(id int,jibin varchar(20))
insert into b
select 1,' 糖尿病'
union all
select 1, '高血壓'
union all
select 2, ' 高血壓'
go
alter function fun_str(@id int)
returns varchar(200)
begin
declare @str varchar(200),@exec varchar(8000)
set @str=''
select @str=@str+','+jibin from b where id=@id
set @str=stuff(@str,1,1,'')
return (@str)
end
go
select *,dbo.fun_str(id) as jibin from a
/*
id name jibin
------------------------------------------------------
1 張XX 糖尿病,高血壓
2 李XX 高血壓
*/
Create Table A
(ID Int,
姓名 Nvarchar(20))
Insert A Select 1, N'张XX'
Union All Select 2, N'李XX'
Union All Select 3, N'王XX'
Create Table B
(ID Int,
疾病 Nvarchar(20))
Insert B Select 1, N'糖尿病'
Union All Select 1, N'高血压'
Union All Select 2, N'糖尿病'
Union All Select 3, N'感冒'
GO
--1。既得糖尿病又得高血压的人
Select A.*
From A
Inner Join B On A.ID = B.ID
Inner Join B C On A.ID = C.ID
Where B.疾病 = N'糖尿病' And C.疾病 = N'高血压'
-- 2。得糖尿病却没有得高血压的人
Select A.*
From A
Inner Join B On A.ID = B.ID
Where B.疾病 = N'糖尿病'
And Not Exists(Select ID From B Where ID = A.ID And 疾病 = N'高血压')
--3。既没有得糖尿病又没有得高血压的人
Select A.* From A
Where Not Exists(Select ID From B Where ID = A.ID And 疾病 = N'糖尿病')
And Not Exists(Select ID From B Where ID = A.ID And 疾病 = N'高血压')
GO
Drop Table A, B
--Result
/*
--1結果
ID 姓名
1 张XX
--3.
Select A.* From A
Where Not Exists(Select ID From B Where ID = A.ID And 疾病 = '糖尿病')
And Not Exists(Select ID From B Where ID = A.ID And 疾病 = '高血压')