Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to integrate two tables according to two columns of information in SQL". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The data of the two tables are as follows:

-2017

IdcollegescoreA001 Peking University 670A002 Renmin University of China 646A003 Tsinghua University 664A003 Tsinghua University (orientation)

A004 Beijing Jiaotong University 615A004 Beijing Jiaotong University (Sino-foreign cooperative education)

A005 Beijing University of Technology

A005 Beijing University of Technology (Sino-foreign cooperation in running schools)

-- 2018

IdcollegescoreA001 Peking University 680A002 Renmin University of China 662A003 Tsinghua University 671A003 Tsinghua University (University specific requirements)

A004 Beijing Jiaotong University 634A004 Beijing Jiaotong University (Sino-foreign cooperative education)

A005 Beijing University of Technology

A005 Beijing University of Technology (Sino-foreign cooperation in running schools)

A006 640A007 of Beijing University of Aeronautics and Astronautics Beijing Institute of Technology 636A007 Beijing Institute of Technology (Sino-foreign cooperative education)

A008 632Y007 Beijing Institute of Technology of University of Science and Technology Beijing

Requirements, new table four columns, id college,s2017,s2018 two tables integrated, according to id, college

The relevant statements are as follows:

-- create table create table score2017 (id varchar2 (10), college varchar2 (60), s2017 int); create table score2018 (id varchar2 (10), college varchar2 (60), s2018 int);-- create collection table create table score1718 (id varchar2 (10), college varchar2 (60), s2017 int,s2018 int);-- delete duplicate schools and iddelete from score2017 where replace (college,'',') = 'Guangxi University (Professional Volunteer)' in 2017 table. -- 5 lines delete from score2017 where replace (college,'','') = 'Hebei normal University (professional volunteer)';-- 2 rows-- insert 1138 rows of the same data in two tables: insert into score1718 select a. ID _ replace (a. B where replace,''), a.s2017 ~, b.s2018 from score2017 a _ dint score2018 b where replace (a. B where replace _ _,') = replace (b. -- 1138 rows of duplicated data-- insert 80 rows of different data in the 2017 table insert into score1718 value (id,college,s2017) select a.id replaceReciproces (a.not in',''), a.s2017 from score2017 a where replace (a.not in',') not in (select replace (a.not in',') from score2017 a score2018 b where replace (a.accoungeforce','') = replace (b. )-- insert 134rows of different data in the 2018 table insert into score1718 value (id,college,s2018) select b. Id replace (b. Not in), b.s2018 from score2018 b where replace (b. )-- insert 8 rows of 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 in the table of the same school and different id data in 2018 (select a.id from score2017 a score 2018 b where replace (a. And a.id=b.id) = replace (b.) select count (college) from score2017)-- comparison data -- 1218 rows of select count (college) from score2018;-- 1280 '1138' 134' 8color-- the total data in the collection table is 1360 rows of select count (college) from score1718;-- 1360' 1138' 80' 134' 8 rows-- add type column, extract field alter table score1718 add (CollegeType varchar2 (40)); update score1718 set CollegeType=' normal 'where college not like'% (%)%' -- 982 lines update score1718 set CollegeType=substr (college,instr (college,' (') + 1je instr (college,')')-instr (college,' (')-1) where college like'% (%)%';-- 378 lines select id,college,CollegeType from score1718 The results are as follows: id college s2017 s2018 CollegeTypeA650 Sichuan Foreign Studies University 582 608 General A651 Southwest University of Finance and Economics 619 638 General A652 Southwest University of political Science and Law 612 627 ordinary A652 Southwest University of political Science and Law (Sino-foreign cooperative school) 599 624 Sino-foreign cooperatively run A653 Chengdu Institute of physical Education 540 General A655 Sichuan Academy of Fine Arts 549 570 General A656 Southwest University for nationalities 556 582 ordinary A657 Guizhou University 586 601 ordinary A660 Guizhou Medical University ordinary "how to integrate two tables based on two columns of information in SQL" is introduced here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.