代码
--create table
create table VCMDISTRICTINTERFACE_1214_1622
(
DISTRICTNO VARCHAR2(20) not null,
DISTRICTCODE VARCHAR2(6) not null,
IDISTRICTCODE VARCHAR2(20),
SOURCEFLAG VARCHAR2(2),
DESCONE VARCHAR2(1)
)
--create table
create table VCMDISTRICTCODE
(
DISTRICTNO VARCHAR2(20) not null,
DISTRICTCODE VARCHAR2(6) not null,
DISTRICTNAME VARCHAR2(200) not null,
DESCRIPTION VARCHAR2(500)
)
--create table
create table AREA
(
ID NUMBER(11) not null,
PARENTID NUMBER(11),
AREANAME VARCHAR2(64)
)
--create tmp table,临时表中保存城市名称,以及出现次数
create table tmp_1215 as
select trim(t2.districtname)as nam,count(*) as com from vcmdistrictinterface_1214_1622 t1
left join vcmdistrictcode t2 on t2.districtcode=t1.districtcode
group by t2.districtname having count(*)>0
--create tmp table,
create table tmp_1216
as select t4.areaname as nam,count(*) as com from area t4
group by t4.areaname having count(*)>0
--查找临时表中,城市名称相同,但出现次数不同的记录
select * from tmp_1215 t15
left join tmp_1216 t16 on t16.nam=t15.nam and t15.com<> t16.com
create table VCMDISTRICTINTERFACE_1214_1622
(
DISTRICTNO VARCHAR2(20) not null,
DISTRICTCODE VARCHAR2(6) not null,
IDISTRICTCODE VARCHAR2(20),
SOURCEFLAG VARCHAR2(2),
DESCONE VARCHAR2(1)
)
--create table
create table VCMDISTRICTCODE
(
DISTRICTNO VARCHAR2(20) not null,
DISTRICTCODE VARCHAR2(6) not null,
DISTRICTNAME VARCHAR2(200) not null,
DESCRIPTION VARCHAR2(500)
)
--create table
create table AREA
(
ID NUMBER(11) not null,
PARENTID NUMBER(11),
AREANAME VARCHAR2(64)
)
--create tmp table,临时表中保存城市名称,以及出现次数
create table tmp_1215 as
select trim(t2.districtname)as nam,count(*) as com from vcmdistrictinterface_1214_1622 t1
left join vcmdistrictcode t2 on t2.districtcode=t1.districtcode
group by t2.districtname having count(*)>0
--create tmp table,
create table tmp_1216
as select t4.areaname as nam,count(*) as com from area t4
group by t4.areaname having count(*)>0
--查找临时表中,城市名称相同,但出现次数不同的记录
select * from tmp_1215 t15
left join tmp_1216 t16 on t16.nam=t15.nam and t15.com<> t16.com