Shulou(Shulou.com)06/01 Report--

Today, I would like to talk to you about how to use SQL for set operations, many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

1. Compare tables and tables drop table if exists tbl_a;create table tbl_a (key1 varchar (10), col_1 int4,col_2 int4,col_3 int4); insert into tbl_a values ('Aids, 2, 3, 4); insert into tbl_a values (' bands, 0, 7, 9); insert into tbl_a values ('cations, 5, 1, 6); drop table if exists tbl_b Create table tbl_b (key1 varchar (10), col_1 int4,col_2 int4,col_3 int4); insert into tbl_b values ('Aids, 2, 3, 4); insert into tbl_b values (' Bones, 0, 7, 9); insert into tbl_b values ('cations, 5, 1, 6) -- # # if the number of union a b rows is the same, then the two tables are equal select count (1) row_cnt from (select * from tbl_A union select * from tbl_b) tmp

Directly find the difference between the two tables.

(select * from tbl_a except select * from tbl_b) union all (select * from tbl_b except select * from tbl_a); 2. Use difference sets to realize relational division

Build a table

Drop table if exists skills;create table skills (skill varchar (10)); insert into skills values ('oracle'); insert into skills values (' unix'); insert into skills values ('java'); drop table if exists empskills;create table empskills (emp varchar (10), skill varchar (10)); insert into empskills values (' oracle''); insert into empskills values ('Xiangtian', 'unix'); insert into empskills values (' Xiangtian', 'java'); insert into empskills values (' Xiangtian', 'Che') Insert into empskills values ('Magic', 'oracle''); insert into empskills values ('Magic', 'unix'); insert into empskills values (' Magic', 'java'); insert into empskills values (' Hirai', 'oracle'); insert into empskills values (' Hirai', 'unix'); insert into empskills values (' Hirai', 'PHP'); insert into empskills values (' Hirai', 'Perl'); insert into empskills values (' Hirai', 'Clearing') Insert into empskills values ('Ruotian', 'Perl'); insert into empskills values (' Dulai, 'oracle');-- change division into subtraction select distinct emp from empskills es1 where not exists (select skill from skills expect select skill from empskills es2 where es1.emp = es2.emp); 3. Find an equivalent subset drop table if exists supparts;create table supparts (sup varchar (10), part varchar (10)) Insert into supparts values ('screws,'); insert into supparts values ('nuts, nuts'); insert into supparts values ('tubes,'); insert into supparts values ('tubes,' screws'); insert into supparts values ('tubes,' screws'); insert into supparts values ('screws, screws'); insert into supparts values ('screws, nuts'); insert into supparts values ('screws, tubes') Insert into supparts values ('screws,'); insert into supparts values ('pipes,'); insert into supparts values ('fuses'); insert into supparts values ('nuts'); insert into supparts values ('fuses'); insert into supparts values ('fuses')

Idea: both suppliers operate the same type of parts (simply connect according to the part column) the two suppliers have the same number of part types (that is, there is an one-to-one mapping) (count limit)

Select a.sup S1, b.sup S2 from supparts a, supparts b where a.sup < b.sup-generate all combinations of suppliers and a.part = b.part-condition 1: operate the same type of parts group by a.sup B.suphaving count (*) = (select count (1)-condition 2: the number of parts operated is the same a = intermediate number from supparts c where c.sup = a.sup) and count (*) = (select count (1)-condition 2: the quantity type of operating parts is the same b = intermediate number From supparts d where d.sup = b.sup) 4. Delete Zongxing drop table if exists products;create table products (rowid int4,name1 varchar (10), price int4); insert into products values (1dint 'Apple', 50); insert into products values (2dre 'Orange', 100); insert into products values (3jingle 'Orange', 100); insert into products values (4Jing 'Orange', 100); insert into products values (5Jing 'Banana', 80) Delete the efficient SQL statement (1): use EXCEPT to complement the set delete from productswhere rowid in (select rowid-- all rowid from products except-- minus select max (rowid)-- the rowid from products group by name1 to be left Price) Delete re-line efficient SQL statements (2): through not indelete from products where rowid not in (select max (rowid) from products group by name1, price) Exercise-improving the comparison of union select case when count (1) = (select count (1) from tbl_A) and count (1) = (select count (1) + 1 from tbl_b) then count (1) else 'unequal' end row_cnt from (select * from tbl_A union select * from tbl_b) tmp After reading the above, do you have any further understanding of how to do set operations with SQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.