--再加上distinct去重复:select distinct TaskID from t_task a where (select count(*) from t_task where TaskID=a.TaskID and tvalue=0)=4
--测试--测试数据 create table t_task(ID int identity(1,1),TaskID int,TValue int) insert t_task select 1,0 union all select 1,0 union all select 1,1 union all select 1,2 union all select 1,0 union all select 2,0 union all select 2,0 union all select 2,0 union all select 2,0 union all select 3,1 union all select 3,0 union all select 3,0 go--查询 select distinct TaskID from t_task a where (select count(*) from t_task where TaskID=a.TaskID and tvalue=0)=4 go--删除测试 drop table t_task/*--测试结果 TaskID ----------- 2(所影响的行数为 1 行) --*/
select TaskID from t_task where tvalue=0 group by TaskID having count(*)=4
不好意思﹐還沒寫完﹐點錯按鈕...select distinct a.TaskID from t_task a join t_task b on a.TaskID=b.TaskID where a.tvalue=0 group by a.TaskID,a.ID having count(a.TaskID)=4
from t_task a
where (select count(*) from t_task where TaskID=a.TaskID and tvalue=0)=4
create table t_task(ID int identity(1,1),TaskID int,TValue int)
insert t_task
select 1,0
union all select 1,0
union all select 1,1
union all select 1,2
union all select 1,0
union all select 2,0
union all select 2,0
union all select 2,0
union all select 2,0
union all select 3,1
union all select 3,0
union all select 3,0
go--查询
select distinct TaskID
from t_task a
where (select count(*) from t_task where TaskID=a.TaskID and tvalue=0)=4
go--删除测试
drop table t_task/*--测试结果
TaskID
-----------
2(所影响的行数为 1 行)
--*/
from t_task
where tvalue=0
group by TaskID
having count(*)=4