oracle 两表两列数据对比_【SQL】根据两列信息，整合两张表数据

两张表数据如下：

--2017年

id

college

score

A001

北京大学

670

A002

中国人民大学

646

A003

清华大学

664

A003

清华大学         (定向)

A004

北京交通大学

615

A004

北京交通大学     (中外合作办学)

A005

北京工业大学

A005

北京工业大学     (中外合作办学)

--2018年

id

college

score

A001

北京大学

680

A002

中国人民大学

662

A003

清华大学

671

A003

清华大学         (院校特定要求)

A004

北京交通大学

634

A004

北京交通大学     (中外合作办学)

A005

北京工业大学

A005

北京工业大学     (中外合作办学)

A006

北京航空航天大学

640

A007

北京理工大学

636

A007

北京理工大学     (中外合作办学)

A008

北京科技大学

632

Y007

北京理工大学

621

需求，新表四列， id  college,s2017,s2018  两张表整合在一起，根据id、college

相关语句如下：

--创建2017/2018表

create table score2017 (id varchar2(10),college varchar2(60),s2017 int);

create table score2018 (id varchar2(10),college varchar2(60),s2018 int);

--创建集合表

create table score1718 (id varchar2(10),college varchar2(60),s2017 int,s2018 int);

--删除2017年表中重复学校和id

delete from score2017 where replace(college,' ','')='广西大学(专业志愿)';  --5行

delete from score2017 where replace(college,' ','')='河北师范大学(专业志愿)';  --2行

--插入两张表相同数据  1138行

insert into score1718 select a.id,replace(a.college,' ',''),a.s2017,b.s2018 from score2017 a,score2018 b where replace(a.college,' ','')=replace(b.college,' ','') and a.id=b.id

select count(college) from score1718;  --1138 行重复数据

--插入2017年表中不相同数据  80行

insert into score1718 value(id,college,s2017) select a.id,replace(a.college,' ',''),a.s2017 from score2017 a where replace(a.college,' ','') not in (select replace(a.college,' ','') from score2017 a,score2018 b where replace(a.college,' ','')=replace(b.college,' ',''))

--插入2018年表中不相同数据  134行

insert into score1718 value(id,college,s2018) select b.id,replace(b.college,' ',''),b.s2018 from score2018 b where replace(b.college,' ','') not in (select replace(a.college,' ','') from score2017 a,score2018 b where replace(a.college,' ','')=replace(b.college,' ',''))

--插入2018年表中相同学校不相同id数据   8行

insert into score1718 value(id,college,s2018) select id,college,s2018 from score2018 b where b.college in (select college from score2018 group by college having count(*) > 1) and b.id not in(select a.id from score2017 a,score2018 b where replace(a.college,' ','')=replace(b.college,' ','') and a.id=b.id)

--对比数据

select count(college) from score2017;  --1218行

select count(college) from score2018;  --1280=1138+134+8

--集合表中总数据为1360行

select count(college) from score1718;   --1360=1138+80+134+8行

--添加类型列，提取字段

alter table score1718 add (CollegeType varchar2(40));

update score1718 set CollegeType='普通' where college not like '%(%)%';  --982行

update score1718 set CollegeType=substr(college,instr(college,'(')+1,instr(college,')')-instr(college,'(')-1) where  college like '%(%)%';  --378行

select id,college,CollegeType from score1718;

--结果如下所示：

idcolleges2017 s2018  CollegeType

A650四川外国语大学582   608普通

A651西南财经大学619   638普通

A652西南政法大学612   627普通

A652西南政法大学(中外合作办学)599   624中外合作办学

A653成都体育学院540   普通

A655四川美术学院549   570普通

A656西南民族大学556   582普通

A657贵州大学586   601普通

A660贵州医科大学   普通