久久精品国产亚洲AV无码娇色 ,日韩精品久久久肉伦网站,久久青青草原精品国产

這篇文章主要介紹了關(guān)于用SQL進(jìn)行集合運(yùn)算，有著一定的參考價值，現(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('度來','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','保險絲');  insert?into?supparts?values('E',?'螺母');  insert?into?supparts?values('E',?'管子');  insert?into?supparts?values('F','保險絲');

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

select?a.sup?s1,?b.sup?s2??from?supparts?a,?supparts?b?where?a.sup?<h2>4、刪除重行</h2><pre class="brush:sql;toolbar:false;">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求補(bǔ)集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  ????????????????????);

練習(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  ;