用SQL進(jìn)行集合運(yùn)算

這篇文章主要介紹了關(guān)于用SQL進(jìn)行集合運(yùn)算 ，有著一定的參考價(jià)值，現(xiàn)在分享給大家，有需要的朋友可以參考一下

1、比較表和表

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 行數(shù)一致則兩張表相等 select count(1) row_cnt  from ( select * 
           from tbl_A           union 
          select *            from tbl_b
        ) tmp
;

直接求兩表的不同之處

(select * from tbl_a except
 select * from tbl_b) union all
 (select * from tbl_b  except
  select * from tbl_a);

2、用差集實(shí)現(xiàn)關(guān)系除法運(yùn)算

建表

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('度來(lái)','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、尋求相等的子集

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','保險(xiǎn)絲');
insert into supparts values('E', '螺母');
insert into supparts values('E', '管子');
insert into supparts values('F','保險(xiǎn)絲');

思路： 兩個(gè)供應(yīng)商都經(jīng)營(yíng)同種類(lèi)型的零件 （簡(jiǎn)單的按照零件列進(jìn)行連接） 兩個(gè)供應(yīng)商的零件類(lèi)型數(shù)相同（即存在一一映射）（count限定）

select a.sup s1, b.sup s2  from supparts a, supparts b where a.sup < b.sup                       -- 生成供應(yīng)商的全部組合 
   and a.part = b.part                     -- 條件1：經(jīng)營(yíng)同種類(lèi)型的零件 
 group by a.sup, b.suphaving count(*) = (select count(1)         -- 條件2：經(jīng)營(yíng)的零件的數(shù)量種類(lèi)相同 a = 中間數(shù)                     from supparts c                    where c.sup = a.sup)   and count(*) = (select count(1)         -- 條件2：經(jīng)營(yíng)的零件的數(shù)量種類(lèi)相同 b = 中間數(shù)                     from supparts d                    where d.sup = b.sup)
;

4、刪除重行

drop table if exists products;create table products(
rowid int4,
name1 varchar(10),
price int4
);insert into products values(1,'蘋(píng)果',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語(yǔ)句（1）：通過(guò)EXCEPT求補(bǔ)集delete from productswhere rowid  in (select rowid           -- 全部rowid                   from products 
                 except                 -- 減去                 select max(rowid)      -- 要留下的rowid                   from products                  group by name1, price
                  );-- 刪除重行高效SQL語(yǔ)句（2）：通過(guò)not indelete from products where rowid not in (select max(rowid)                      from products                     group by name1, price
                    );

練習(xí)

-- 改進(jìn)中用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
;

內(nèi)容多來(lái)自 《SQL進(jìn)階教材》，僅做筆記。練習(xí)部分代碼均為原創(chuàng)。

以上就是用SQL進(jìn)行集合運(yùn)算 的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！