关于sql查询
病例 症状
a A
a B
a C
b B
b B
c A
c B
c C
d A
d B
查询出同时有症状A B C 的病例。
如果说症状是不定的可能有n种，写成存储过程又该怎么写呢？ sql 存储过程??查询
[解决办法]
create table #tb(病例 varchar(10), 症状 varchar(10))
insert into #tb
select 'a','A'
union all select 'a','B'
union all select 'a','C'
union all select 'b','B'
union all select 'b','B'
union all select 'c','A'
union all select 'c','B'
union all select 'c','C'
union all select 'd','A'
union all select 'd','B'
select 病例
from
(select 病例,count(*) as num
from (select distinct * from #tb)a
group by 病例
)a,
(select count(*) as total from (select distinct 症状 from #tb)t)b
where a.num=b.total
drop table #tb
--结果
/*
a
c
*/
select * from (
select ta.bl,ta.zz as zza,tb.zz as zzb,tc.zz as zzc
from (select 病例 as bl,'A' zz from yourTable where 症状='A') as ta
left join (select 病例 as bl,'B' zz from yourTable where 症状='B') as tb on ta.bl=tb.bl
left join (select 病例 as bl,'C' zz from yourTable where 症状='C') as tc on ta.bl=tc.bl
) as t
where t.zza is not null
and t.zzb is not null
and t.zzc is not null
create table #tb(病例 varchar(10), 症状 varchar(10)) insert into #tb select 'a','A'union all select 'a','B'union all select 'a','C'union all select 'b','B'union all select 'b','B'union all select 'c','A'union all select 'c','B'union all select 'c','C'union all select 'd','A'union all select 'd','B'
go
select 病例 from (
select 病例, 症状=stuff((select ','+症状 from #tb t where 病例=#tb.病例 for xml path('')), 1, 1, '')
from #tb
group by 病例
)a where 症状='A,B,C'
GO
DROP TABLE #tb
