我的意思是有两个结构一样的表,这种表有这两个字段标识号段的
begin_acc_nbr,end_acc_nbr,比如
begin_acc_nbr,end_acc_nbr
--------------
52660000 52669999 改行就代表5266号段
begin_acc_nbr,end_acc_nbr,比如
begin_acc_nbr,end_acc_nbr
--------------
52660000 52669999 改行就代表5266号段
prompt PL/SQL Developer import file
prompt Created on 2006年1月24日 by wugang
set feedback off
set define off
prompt Dropping A_1...
drop table A_1 cascade constraints;
prompt Dropping A_2...
drop table A_2 cascade constraints;
prompt Creating A_1...
create table A_1
(
BEGIN VARCHAR2(8),
END VARCHAR2(8)
)
tablespace USERS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);prompt Creating A_2...
create table A_2
(
BEGIN VARCHAR2(8),
END VARCHAR2(8)
)
tablespace USERS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
);prompt Disabling triggers for A_1...
alter table A_1 disable all triggers;
prompt Disabling triggers for A_2...
alter table A_2 disable all triggers;
prompt Loading A_1...
insert into A_1 (BEGIN, END)
values ('1111000', '1111999');
insert into A_1 (BEGIN, END)
values ('1112000', '1112999');
insert into A_1 (BEGIN, END)
values ('1113000', '1113999');
insert into A_1 (BEGIN, END)
values ('1114000', '1114999');
insert into A_1 (BEGIN, END)
values ('1115000', '1115999');
insert into A_1 (BEGIN, END)
values ('1116000', '1116999');
insert into A_1 (BEGIN, END)
values ('1117000', '1117999');
insert into A_1 (BEGIN, END)
values ('1118000', '1118999');
insert into A_1 (BEGIN, END)
values ('1119000', '1119999');
insert into A_1 (BEGIN, END)
values ('1110000', '1110999');
insert into A_1 (BEGIN, END)
values ('1200000', '1200999');
commit;
prompt 11 records loaded
prompt Loading A_2...
insert into A_2 (BEGIN, END)
values ('1110000', '1119999');
insert into A_2 (BEGIN, END)
values ('2220000', '2229999');
insert into A_2 (BEGIN, END)
values ('3330000', '3339999');
commit;
prompt 3 records loaded
prompt Enabling triggers for A_1...
alter table A_1 enable all triggers;
prompt Enabling triggers for A_2...
alter table A_2 enable all triggers;
set feedback on
set define on
prompt Done.1、重叠部分的查询:
select t1.* from
(
select substr(t.begin,0,3) as type,min(t.begin) as begin,max(t.end) as end
from a_1 t
group by substr(t.begin,0,3)
) t1,
(
select substr(t.begin,0,3) as type,min(t.begin) as begin,max(t.end) as end
from a_2 t
group by substr(t.begin,0,3)
) t2
where t1.type=t2.type
2、不一致的查询SQL:
(
(
select substr(t.begin,0,3) as type,min(t.begin) as begin,max(t.end) as end
from a_1 t
group by substr(t.begin,0,3)
)
union
(
select substr(t.begin,0,3) as type,min(t.begin) as begin,max(t.end) as end
from a_2 t
group by substr(t.begin,0,3)
)
)
minus
(
(
select substr(t.begin,0,3) as type,min(t.begin) as begin,max(t.end) as end
from a_1 t
group by substr(t.begin,0,3)
)
INTERSECT
(
select substr(t.begin,0,3) as type,min(t.begin) as begin,max(t.end) as end
from a_2 t
group by substr(t.begin,0,3)
)
)