比如Parent与Sun是一对多的关系
ID Parent SUN
1 1 live
2 1 live
3 1 dead
4 1 dead5 2 live
6 2 live
7 2 live8 3 dead
9 3 dead
10 3 live我想找出sun字段部分为live,部分为dead的Parent值
对应上表,结果就是
1
3因为2的sun字段全是live的,不选
ID Parent SUN
1 1 live
2 1 live
3 1 dead
4 1 dead5 2 live
6 2 live
7 2 live8 3 dead
9 3 dead
10 3 live我想找出sun字段部分为live,部分为dead的Parent值
对应上表,结果就是
1
3因为2的sun字段全是live的,不选
from TableName a
where exists(select * from TableName where Parent=a.Parent and SUN='live')
and exists(select * from TableName where Parent=a.Parent and SUN='dead')
select parent from [Table]
group by parent having count(distinct sun)=2
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
ID int,
Parent int,
SUN char(5)
)
go
--插入测试数据
insert into tb select 1,1,'live'
union all select 2,1,'live'
union all select 3,1,'dead'
union all select 4,1,'dead'
union all select 5,2,'live'
union all select 6,2,'live'
union all select 7,2,'live'
union all select 8,3,'dead'
union all select 9,3,'dead'
union all select 10,3,'live'
go
--代码实现select Parent
from tb
group by Parent
having count(*)>sum(case SUN when 'live' then 1 else 0 end)/*测试结果Parent
-----------
1
3(2 行受影响)
*/
好像不太正确, 如果把最后一行的sun改成dead,得出的结果是不对的