表A:字段:id,name,status1,status2,status3;
name varchar;status1,status2,status3 枚举:0,1.
要取得这样的记录 status1,status2,status3 都为0.
举例:
id name status1,status2,status3
1 aa 0 0 1
2 aa 0 0 0
3 bb 1 0 1
4 bb 0 0 0
5 cc 0 0 0
6 cc 0 0 0
以上五条记录 只有 cc满足条件
------解决方案--------------------
其实只要把符合条件的name取出来就可以了,status1,status2,status3肯定是0就不要取了
- SQL code
select t.name from tb1 t group by t.name having(sum(t.status1) = 0 and sum(t.status2) = 0 and sum(t.status3) = 0);
------解决方案--------------------
[code=SQL][/code]select * from t1 a where a.name
not in (select name from t1 where stu1<>0 or stu2<>0 or stu3<>0)
------解决方案--------------------
SELECT DISTINCT name
FROM test1 t1
WHERE NOT EXISTS(
SELECT *
FROM test1 t2
WHERE t1.name = t2.name
AND (t2.status1 = 1 OR t2.status2 = 1 OR t2.status3 = 1))
------解决方案--------------------
- SQL code
select a.* from yourtable a where not exists (select 1 from yourtable where name = a.name and (status1 = 1 or status2 = 1 or status3 = 1) )