帮忙写个查询语句,好人一生平安!

[smilesjiang]

ID FLAG
1 Y
1 Y
1 N

2 Y
2 N
2 Y

3 Y
3 Y
3 Y

4 N
4 N
4 N
我要找出FLAG全是Y的,也就是查询结果是3,如何查询?

[FlySQL]

select * from tb t where not exists(select 1 from tb where id=t.id and flag!='Y')

[FlySQL]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[FLAG] varchar(1))
insert [tb]
select 1,'Y' union all
select 1,'Y' union all
select 1,'N' union all
select 2,'Y' union all
select 2,'N' union all
select 2,'Y' union all
select 3,'Y' union all
select 3,'Y' union all
select 3,'Y' union all
select 4,'N' union all
select 4,'N' union all
select 4,'N'
 
select * from tb t where not exists(select 1 from tb where id=t.id and flag!='Y')

/**
ID          FLAG
----------- ----
3           Y
3           Y
3           Y

(3 行受影响)
**/

[-晴天]

select id from tb a where not exists(select 1 from tb where flag='N' and id=a.id)

[cd731107]

select * from tb a
where a.FLAG='Y'
and not exists
(select 1 from tb b where a.id=b.id and b.FLAG<>'Y)'

[smilesjiang]

最后一个答案,最周密!!

[jordy127]

select * from tb t where not exists ( select * from tb where id=t.id and flag='N'