你是只查N2? select * from 表 where (status=0 or status=1) and Name='N2'
--创建测试数据 create table test1 ( status char(1), name varchar2(20) ) insert into test1 values('0','N1'); insert into test1 values('1','N1'); insert into test1 values('2','N1');insert into test1 values('0','N2'); insert into test1 values('1','N2');insert into test1 values('0','N3'); --查询语句 select t1.* from test1 t1 where t1.status<>'2' and not exists ( select 1 from test1 t2 where t1.name=t2.name and t2.status='2' )--结果: 1 1 N2 2 0 N2 3 0 N3
可能是我的问题,N3的记录也不要,只要staust =0 or 1的N2的记录 N3的状态只有0,没有1
select * from (select count(*) as c,Name from AA group by name) z where z.c=2
with tb as( select 0 Status ,'N1' Name from dual union all select 0 ,'N1' from dual union all select 1 ,'N1' from dual union all select 2 ,'N1' from dual union all select 0 ,'N2' from dual union all select 1 ,'N2' from dual union all select 0 ,'N3' from dual ) select * from tb where name in( select name from( select Name, count(1) cnt ,--同一Name的个数 sum(Status) sum_Status--同一Name的Status和 from tb where Name not in( select distinct Name from tb where Status <>1 and Status<>0 )--排除不是1和0的Name group by Name) where cnt>=sum_Status --个数比和大,说明有0 and sum_Status<>0--和不为0,说明有1 )
select * from 表 where (status=0 or status=1) and Name='N2'
select * from 表 where (status=0 or status=1) and Name='N2'
--创建测试数据
create table test1
(
status char(1),
name varchar2(20)
)
insert into test1 values('0','N1');
insert into test1 values('1','N1');
insert into test1 values('2','N1');insert into test1 values('0','N2');
insert into test1 values('1','N2');insert into test1 values('0','N3');
--查询语句
select t1.* from test1 t1
where t1.status<>'2' and not exists
(
select 1 from test1 t2 where t1.name=t2.name and t2.status='2'
)--结果:
1 1 N2
2 0 N2
3 0 N3
N3的状态只有0,没有1
select 0 Status ,'N1' Name from dual union all
select 0 ,'N1' from dual union all
select 1 ,'N1' from dual union all
select 2 ,'N1' from dual union all
select 0 ,'N2' from dual union all
select 1 ,'N2' from dual union all
select 0 ,'N3' from dual )
select * from tb
where name in(
select name from(
select Name,
count(1) cnt ,--同一Name的个数
sum(Status) sum_Status--同一Name的Status和
from tb
where Name not in(
select distinct Name from tb where Status <>1 and Status<>0 )--排除不是1和0的Name
group by Name)
where cnt>=sum_Status --个数比和大,说明有0
and sum_Status<>0--和不为0,说明有1
)