有一组号码,比如
1234A
1234B
f8976A
f8976B
98987777A
dafdj98B
98978787
f099788
要求要查询到末尾数字单独为A和B的记录,像1234A,1234B成对出现就要,而98987777A单独出现A的这种情况就需要,dafdj98B末尾单独为B的数据也需要,请各位朋友帮下忙
1234A
1234B
f8976A
f8976B
98987777A
dafdj98B
98978787
f099788
要求要查询到末尾数字单独为A和B的记录,像1234A,1234B成对出现就要,而98987777A单独出现A的这种情况就需要,dafdj98B末尾单独为B的数据也需要,请各位朋友帮下忙
1234A
1234B
f8976A
f8976B
98987777A
dafdj98B
98978787
f099788
要求要查询dhhm末尾数字单独为A和B的记录,像1234A,1234B成对出现就要,而98987777A单独出现A的这种情况就需要,dafdj98B末尾单独为B的数据也需要,请各位朋友帮下忙
where substr(a.num,-1)='A'
or substr(b.num,-1)='B'
你说都要的话.就这样!
select num from tablename
where substr(num,-1)='A'
or substr(num,-1)='B'
where substr(num,-1) in('A','B')
from tbl a
where substr(a.dhhm,-1) in('A','B')
and not exists(
select 0
from tlb b
where substr(b.dhhm,1,length(b.dhhm)-1) = substr(a.dhhm,1,length(a.dhhm)-1)
);
是怎么得来的?tbl a是我本身的表,b是怎么构建出来的?
where (substr(shuju,-1,1)='B'
and not exists(select 1 from pyk where shuju = (substr(a.shuju,1,length(a.shuju) - 1)||'A')))
or
(substr(shuju,-1,1)='A'
and not exists(select 1 from pyk where shuju = (substr(a.shuju,1,length(a.shuju) - 1)||'B')));
SQL> create table pyk(shuju varchar2(20));Table created
SQL> insert into pyk values('987866A');1 row insertedSQL> insert into pyk values('987866B');1 row insertedSQL> insert into pyk values('7866B');1 row insertedSQL> insert into pyk values('786A');1 row insertedSQL> insert into pyk values('99999999');1 row insertedSQL> insert into pyk values('99999999A');1 row insertedSQL> insert into pyk values('99999999B');1 row insertedSQL> commit;
表pyk的数据为:
987866A
987866B
7866B
786A
99999999
99999999A
99999999B
执行后的结果为:
7866B
786A
谢谢各位的帮助