desc time
------- -----
... ...
B 1
E 2
B 3
E 4
B 5
E 6
B 7
B 8
E 9
B A
B B
E C
B D
E F
... ...
正常的应是(B)egin, 后一行即为(E)nd, 现要找出不正常的情况(Begin后没End):
B 7
B A
who to do?
...全文
4117打赏收藏
搞数据库的看家本领:一个SQL如何些?
我有一个表的模型如下, desc time ------- ----- ... ... B1 E 2 B3 E 4 B5 E 6 B7 B8 E 9 BA BB E C BD E F ... ... 正常的应是(B)egin, 后一行即为(E)nd, 现要找出不正常的情况(Begin后没End): B7 BA who to do?
select * from table a where a.desc='B' and
not exists(select * from table b where b.desc='E' and b.rownum=a.rownum+1)
如果要求E前必须是B
union all
select * from table a where a.desc='E' and
not exists(select * from table b where b.desc='B' and b.rownum=a.rownum-1)
create table skyxj_test(a varchar2(2), b number(5));
insert into skyxj_test values('B', 1);
insert into skyxj_test values('E', 2);
insert into skyxj_test values('B', 3);
insert into skyxj_test values('B', 5);
insert into skyxj_test values('E', 6);
insert into skyxj_test values('B', 7);
insert into skyxj_test values('B', 9);
insert into skyxj_test values('E', 10);
commit;
按问题要求,应该是('B', 3)和('B', 7)为要找的记录,语句:
select * from skyxj_test t1 where a = 'B' and (select t2.a from skyxj_test t2 where t2.b > t1.b and rownum < 2) in ('B');
输出结果为:
SQL>
select * from table A
where desc='B' and not exist
(select * from table
where desc='E' and tonumber(time)=tonumber(A.time)+1);
其中tonumber函数具体名称和用法可能不确切,但算法应该对的。