--查找包含(张,王,李)三人的项目组 select a.* from project a where exists(select 1 from projectman where projectid = a.projectid and name = '张') and exists(select 1 from projectman where projectid = a.projectid and name = '王') and exists(select 1 from projectman where projectid = a.projectid and name = '李') union --查找被(张,王,李)三人包含的项目组 select a.* from project a where a.projectid not in(select distinct projectid from projectman where name not in('张','王','李'))
--查找包含(张,王,李)三人的项目组 select a.* from project a where exists(select 1 from projectman where projectid = a.projectid and name = '张') and exists(select 1 from projectman where projectid = a.projectid and name = '王') and exists(select 1 from projectman where projectid = a.projectid and name = '李') union --查找被(张,王,李)三人包含的项目组 select a.* from project a where not exists(select 1 from projectman where projectid = a.projectid and name not in('张','王','李'))
//--嘿嘿!!我这个比 libin_ftsafe(子陌红尘) 还要简单哈!!!select * from Project where (select count(1) from ProjectMan where ProjectMan.Proid=Project.id and name in('张','王','李') ) = 3
--//生成测试数据------------- select * into #Project from ( select 1 as Id,'项目一组' as 组名 union all select 2 as Id,'项目二组' as 组名 union all select 3 as Id,'项目三组' as 组名 ) Aselect * into #ProjectMan from ( select 1 as Id,1 as ProId,'张' as 成员名 union all select 2 as Id,1 as ProId,'王' as 成员名 union all select 3 as Id,1 as ProId,'李' as 成员名 union all select 3 as Id,2 as ProId,'王' as 成员名 union all select 3 as Id,2 as ProId,'张' as 成员名 ) A--//-Sql如下------------------select * from #Project where ( select count(1) from #ProjectMan where #ProjectMan.Proid=#Project.id and 成员名 in('张','王','李') ) = 3 --//结果:--- id 组名 ------------------ 1 项目一组
select
a.*
from
project a
where
exists(select 1 from projectman where projectid = a.projectid and name = '张')
and
exists(select 1 from projectman where projectid = a.projectid and name = '王')
and
exists(select 1 from projectman where projectid = a.projectid and name = '李')
union
--查找被(张,王,李)三人包含的项目组
select
a.*
from
project a
where
a.projectid not in(select
distinct projectid
from
projectman
where
name not in('张','王','李'))
select
a.*
from
project a
where
exists(select 1 from projectman where projectid = a.projectid and name = '张')
and
exists(select 1 from projectman where projectid = a.projectid and name = '王')
and
exists(select 1 from projectman where projectid = a.projectid and name = '李')
union
--查找被(张,王,李)三人包含的项目组
select
a.*
from
project a
where
not exists(select
1
from
projectman
where
projectid = a.projectid
and
name not in('张','王','李'))
where (select count(1) from ProjectMan
where ProjectMan.Proid=Project.id and name in('张','王','李')
) = 3
select *
into #Project
from
(
select 1 as Id,'项目一组' as 组名
union all
select 2 as Id,'项目二组' as 组名
union all
select 3 as Id,'项目三组' as 组名
) Aselect *
into #ProjectMan
from
(
select 1 as Id,1 as ProId,'张' as 成员名
union all
select 2 as Id,1 as ProId,'王' as 成员名
union all
select 3 as Id,1 as ProId,'李' as 成员名
union all
select 3 as Id,2 as ProId,'王' as 成员名
union all
select 3 as Id,2 as ProId,'张' as 成员名
) A--//-Sql如下------------------select * from #Project
where (
select count(1)
from #ProjectMan
where #ProjectMan.Proid=#Project.id
and 成员名 in('张','王','李')
) = 3
--//结果:---
id 组名
------------------
1 项目一组