表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 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满足条件
with tb1 as(
select 'aa' name,0 status1,0 status2,1 status3 from dual union all
select 'aa' name,0 status1,0 status2,0 status3 from dual union all
select 'bb' name,1 status1,0 status2,1 status3 from dual union all
select 'bb' name,0 status1,0 status2,0 status3 from dual union all
select 'cc' name,0 status1,0 status2,0 status3 from dual union all
select 'cc' name,0 status1,0 status2,0 status3 from dual
)
select tt.name,tt.status1,tt.status2,tt.status3
from(
select name,
(select sum(status1) from tb1 where name=t.name) status1,
(select sum(status2) from tb1 where name=t.name) status2,
(select sum(status3) from tb1 where name=t.name) status3
from tb1 t
group by name) tt
where status1=0 and status2=0 and status3=0;--result
NAME STATUS1 STATUS2 STATUS3
---- ---------- ---------- ----------
cc 0 0 0
with tb1 as(
select 'aa' name,0 status1,0 status2,1 status3 from dual union all
select 'aa' name,0 status1,0 status2,0 status3 from dual union all
select 'bb' name,1 status1,0 status2,1 status3 from dual union all
select 'bb' name,0 status1,0 status2,0 status3 from dual union all
select 'cc' name,0 status1,0 status2,0 status3 from dual union all
select 'cc' name,0 status1,0 status2,0 status3 from dual
)
select distinct name,status1,status2,status3
from tb1
where tb1.name in
(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));
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);
not in (select name from t1 where stu1<>0 or stu2<>0 or stu3<>0)
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))
from yourtable a
where not exists (select 1 from yourtable
where name = a.name
and (status1 = 1 or status2 = 1 or status3 = 1)
)