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 t1id ----------- 3 4(所影响的行数为 2 行)
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 行) */
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 t1id ----------- 3 4(所影响的行数为 2 行)
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 t1id
-----------
3
4(所影响的行数为 2 行)
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 行)
*/
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 t1id
-----------
3
4(所影响的行数为 2 行)