SELECT 条件问题
有如下数据：
ID OSTATUS
1 -1
1 -1
2 0
2 -1
2 1
3 -1
3 0
我想要如下结果，
ID OSTATUS
1 -1
就是状态全部都是-1的ID
[解决办法]
create table 表(id int,OSTATUS int)
insert into 表
select 1,-1
union all select 1,-1
union all select 2,0
union all select 2,-1
union all select 2,1
union all select 3,-1
union all select 3,0
go
select distinct a.* from 表 a
where not exists (select 1 from 表 where id=a.id and OSTATUS!=-1)
/*
id OSTATUS
----------- -----------
1 -1
（所影响的行数为 1 行）
*/
[解决办法]
select distinct a.* from 表 a
where not exists (select 1 from 表 where id=a.id and OSTATUS!=-1)
--这个应该行
[解决办法]
select Did from @tb2 as tb2 left join @tb1 as tb1
on tb1.OIid=tb2.OIid
group by Did,tb2.OIid
having sum(case Ostatus when -1 then 1 else 0 end)=count(Ostatus)
