查找某一分组

[sbsb174]

create table t1(id int, [action] nvarchar)
insert into t1
select 1,'a' union
select 1,'b' uninon
select 1,'c' union
select 1,'over' union
select 2,'a' union
select 2,'b' union
select 2,'over' union
select 3,'a' union
select 3,'b' union
select 4,'a'

此表记录每个id的一组操作，如果action为over表示操作完成
要求：查找出action不为over的id出来，即没有完成的id
结果为：3,4

[dawugui]

create table t1(id int, [action] nvarchar(10)) 
insert into t1 
select 1,'a' union 
select 1,'b' union 
select 1,'c' union 
select 1,'over' union 
select 2,'a' union 
select 2,'b' union 
select 2,'over' union 
select 3,'a' union 
select 3,'b' union 
select 4,'a' 

select distinct id from t1 where id not in (select id from t1 where [action] = 'over')

drop table t1

/*
id          
----------- 
3
4

（所影响的行数为 2 行）
*/

[htl258_Tony]

create table t1(id int, [action] nvarchar(300)) 
insert into t1 
select 1,'a' union 
select 1,'b' union 
select 1,'c' union 
select 1,'over' union 
select 2,'a' union 
select 2,'b' union 
select 2,'over' union 
select 3,'a' union 
select 3,'b' union 
select 4,'a' 

select distinct id from t1 a where not exists(select 1 from t1 where id = a.id and [action]='over')

drop table t1

id          
----------- 
3
4

（所影响的行数为 2 行）

[htl258_Tony]

select * from tb1 where id in(select id from tb1 where [action] <>'over')

[htl258_Tony]

create table t1(id int, [action] nvarchar(300)) 
insert into t1 
select 1,'a' union 
select 1,'b' union 
select 1,'c' union 
select 1,'over' union 
select 2,'a' union 
select 2,'b' union 
select 2,'over' union 
select 3,'a' union 
select 3,'b' union 
select 4,'a' 

select distinct id from t1 where id not in(select id from t1 where [action] ='over')
drop table t1

id          
----------- 
3
4

（所影响的行数为 2 行）