【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;
--結果如下所示：
id			college					s2017 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	貴州醫科大學					   		普通