表a的字段和数据如下:
keyid id
1 1
1 2
1 3
1 11
12 11
12 12
表b是一个日志表,就一个字段,储存了全部a表的Insert语句,数据如下:
keyid_id
insert into a('1','1')
insert into a('1','2')
insert into a('1','3')
insert into a('1','4')
insert into a('12','4')现在要从b表中找出a表不存在的语句
比如找出上面的
insert into a('1','4')
insert into a('12','4')请指点能否用sql实现?
不想用存储过程,谢谢。
keyid id
1 1
1 2
1 3
1 11
12 11
12 12
表b是一个日志表,就一个字段,储存了全部a表的Insert语句,数据如下:
keyid_id
insert into a('1','1')
insert into a('1','2')
insert into a('1','3')
insert into a('1','4')
insert into a('12','4')现在要从b表中找出a表不存在的语句
比如找出上面的
insert into a('1','4')
insert into a('12','4')请指点能否用sql实现?
不想用存储过程,谢谢。
SQL> with A as(
2 select 1 keyid,1 id from dual
3 union
4 select 1,2 from dual
5 union
6 select 1,3 from dual
7 union
8 select 12,11 from dual
9 union
10 select 12,12 from dual
11 ),
12 B as(
13 select 'insert into a(''1'',''1'')' fld from dual
14 union
15 select 'insert into a(''1'',''2'')' fld from dual
16 union
17 select 'insert into a(''1'',''3'')' fld from dual
18 union
19 select 'insert into a(''1'',''4'')' fld from dual
20 union
21 select 'insert into a(''12'',''4'')' fld from dual
22 )
23 select * from b where not exists(
24 select 1 from (
25 select ''''||keyid||''','''||id||'''' v_c from A
26 ) tt where instr(b.fld,tt.v_c)>0
27 );FLD
-----------------------
insert into a('1','4')
insert into a('12','4')SQL>
select 'insert into a('||chr(39)||keyid||chr(39)||','||chr(39)||id||chr(39)||')' from a;