Set operations using SQL

This article mainly introduces the use of SQL for set operations, which has certain reference value. Now I share it with everyone. Friends in need can refer to it

1. Comparison table and table

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('A', 2, 3, 4);
insert into tbl_a values('B', 0, 7, 9);
insert into tbl_a values('c', 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('A', 2, 3, 4);
insert into tbl_b values('B', 0, 7, 9);
insert into tbl_b values('c', 5, 1, 6);-- ## 如果union a b 行数一致则两张表相等 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 set to implement relational division operation

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('相田','unix');
insert into empskills values('相田','java');
insert into empskills values('相田','c#');
insert into empskills values('神奇','oracle');
insert into empskills values('神奇','unix');
insert into empskills values('神奇','java');
insert into empskills values('平井','oracle');
insert into empskills values('平井','unix');
insert into empskills values('平井','PHP');
insert into empskills values('平井','Perl');
insert into empskills values('平井','C++');
insert into empskills values('若田部','Perl');
insert into empskills values('度来','oracle');

--把除法变成减法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. Seek equal subsets

drop table if exists supparts;create table supparts(
sup varchar(10),
part varchar(10)
);insert into supparts values('A', '螺丝');
insert into supparts values('A', '螺母');
insert into supparts values('A', '管子');
insert into supparts values('B', '螺丝');
insert into supparts values('B', '管子');
insert into supparts values('C', '螺丝');
insert into supparts values('C', '螺母');
insert into supparts values('C', '管子');
insert into supparts values('D', '螺丝');
insert into supparts values('D', '管子');
insert into supparts values('E','保险丝');
insert into supparts values('E', '螺母');
insert into supparts values('E', '管子');
insert into supparts values('F','保险丝');

Ideas: Both suppliers deal in the same type of parts (simply connect according to the part column) The number of parts types from the two suppliers is the same (that is, there is a one-to-one mapping) (count limit)

select a.sup s1, b.sup s2  from supparts a, supparts b where a.sup < b.sup                       -- 生成供应商的全部组合 
   and a.part = b.part                     -- 条件1：经营同种类型的零件 
 group by a.sup, b.suphaving count(*) = (select count(1)         -- 条件2：经营的零件的数量种类相同 a = 中间数                     from supparts c                    where c.sup = a.sup)   and count(*) = (select count(1)         -- 条件2：经营的零件的数量种类相同 b = 中间数                     from supparts d                    where d.sup = b.sup)
;

4. Delete duplicate rows

drop table if exists products;create table products(
rowid int4,
name1 varchar(10),
price int4
);insert into products values(1,'苹果',50);insert into products values(2,'橘子',100);
insert into products values(3,'橘子',100);insert into products values(4,'橘子',100);
insert into products values(5,'香蕉',80);-- 删除重行高效SQL语句（1）：通过EXCEPT求补集delete from productswhere rowid  in (select rowid           -- 全部rowid                   from products 
                 except                 -- 减去                 select max(rowid)      -- 要留下的rowid                   from products                  group by name1, price
                  );-- 删除重行高效SQL语句（2）：通过not indelete from products where rowid not in (select max(rowid)                      from products                     group by name1, price
                    );

Exercise

-- 改进中用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 '不相等' end row_cnt  from ( select * from tbl_A          union 
         select * from tbl_b
        ) tmp
;

Most of them come from "SQL Advanced Textbook", just taking notes. Some of the codes in the exercises are original.

The above is the detailed content of Set operations using SQL. For more information, please follow other related articles on the PHP Chinese website!