表A:字段:name,status1,status2,status3;
name varchar;status1,status2,status3 枚举:0,1.要取得这样的记录 status1,status2,status3 都为0.求sql语句
name varchar;status1,status2,status3 枚举:0,1.要取得这样的记录 status1,status2,status3 都为0.求sql语句
select name,status1,status2,status3
from A
where status1=0 and status2=0 and status3=0
name status1,status2,status3
aa 0 0 1
aa 0 0 0
bb 1 0 1
bb 0 0 0
cc 0 0 0
cc 0 0 0
以上五条记录 只有 cc满足条件
请问:aa和bb的记录,什么原因不符合要求呢?
and NOTEXIISTS(SELECT NAME FROM 表 WHERE status1<>0 OR status2<>0 OR status3 <>0 WHERE NAME=A.NAME)
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
select *
from TEST
where TEST.NAME in
(select t.name
from TEST t
group by t.name
having(sum(t.C1) = 0 and sum(t.C2) = 0 and sum(t.C3) = 0));
可以。
但是建议不要这样写,
还是分成2个sql去实现的好。
查找所有status1,status2 都必须为1的,status3都为2, 而且条数是不固定,同一个name下可能有一条,可能有N条 ?不过仍然很感谢你
select *
from test
where TEST.NAME not in
(select t.name
from TEST t
where status1!=0 or status2!=0 or status3!=0 );
CC 0 0 0 好吧
CC 0 0 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))
from A a1
where status1=0 and status2=0 and status3=0 and not exists
(select 1 from A a2 where a2.name=a1.name and (status1=1 or status2=1 or status3=1));
a.status1,
a.status2,
a.status3
from a
where a.name not in(select a.name
from a
where a.status1='1'
or a.status2='1'
or a.status3='1')
这样改下又可以节约判断时间select distinct *
from A a
where a.name not in(select a.name
from A a
where a.status1='1'
or a.status2='1'
or a.status3='1')
and not exists(select 1 from abc b where b.name=a.name and (b.s1<>0 OR b.s2<>0 OR b.s3 <>0))
group by a.name;