成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看

詳解SQL語句的集合運算

數(shù)據(jù)庫
以前總是追求新東西,發(fā)現(xiàn)基礎(chǔ)才是最重要的,今年主要的目標(biāo)是精通SQL查詢和SQL性能優(yōu)化。

以前總是追求新東西,發(fā)現(xiàn)基礎(chǔ)才是最重要的,今年主要的目標(biāo)是精通SQL查詢和SQL性能優(yōu)化。

概述

本篇主要是對集合運算中并集、交集、差集運算基礎(chǔ)的總結(jié)。

集合運算包含四種:

1.并集運算(兩種)

2.交集運算

3.差集運算

下面是集合運算的思維導(dǎo)圖:

 

 

為什么使用集合運算

1.在集合運算中比聯(lián)接查詢和EXISTS/NOT EXISTS更方便。

在閱讀下面的章節(jié)時,我們可以先把環(huán)境準(zhǔn)備好,以下的SQL腳本可以幫助大家創(chuàng)建數(shù)據(jù)庫,創(chuàng)建表,插入數(shù)據(jù)。

一、集合運算

1.集合運算

(1)對輸入的兩個集合或多集進(jìn)行的運算。

(2)多集:由兩個輸入的查詢生成的可能包含重復(fù)記錄的中間結(jié)果集。

(3)T-SQL支持三種集合運算:并集(UNION)、交集(INTERSECT)、差集(EXCEPT)

2.語法

集合運算的基本格式:

輸入的查詢1

集合運算符>

輸入的查詢2

[ORDER BY]

3.要求

(1)輸入的查詢不能包含ORDER BY字句;

(2)可以為整個集合運算結(jié)果選擇性地增加一個ORDER BY字句;

(3)每個單獨的查詢可以包含所有邏輯查詢處理階段(處理控制排列順序的ORDER BY字句);

(4)兩個查詢 必須包含相同的列數(shù);

(5)相應(yīng)列必須具有兼容的數(shù)據(jù)類型。兼容個的數(shù)據(jù)類型:優(yōu)先級較低的數(shù)據(jù)類型必須能隱式地轉(zhuǎn)換為較高級的數(shù)據(jù)類型。比如輸入的查詢1的***列為int類型,輸入的查詢2的***列為float類型,則較低的數(shù)據(jù)類型int類型可以隱式地轉(zhuǎn)換為較高級float類型。如果輸入的查詢1的***列為char類型,輸入的查詢2的***列為datetime類型,則會提示轉(zhuǎn)換失敗:從字符串轉(zhuǎn)換日期和/或時間時,轉(zhuǎn)換失敗;

(6)集合運算結(jié)果中列名由輸入的查詢1決定,如果要為結(jié)果分配結(jié)果列,應(yīng)該在輸入的查詢1中分配相應(yīng)的別名;

(7)集合運算時,對行進(jìn)行進(jìn)行比較時,集合運算認(rèn)為兩個NULL相等;

(8)UNION支持DISTINCT和ALL。不能顯示指定DISTINCT字句,如果不指定ALL,則默認(rèn)使用DISTINCT;

(9)INTERSET和EXCEPT默認(rèn)使用DISTINCT,不支持ALL。

二、UNION(并集)集合運算

1.并集的文氏圖

并集:兩個集合的并集是一個包含集合A和B中所有元素的集合。

圖中陰影區(qū)域代表集合A與集合B的并集

 

 

2.UNION ALL集合運算

(1)假設(shè)Query1返回m行,Query2返回n行,則Query1 UNION ALL Query2返回(m+n)行;

(2)UNION ALL 不會刪除重復(fù)行,所以它的結(jié)果就是多集,而不是真正的集合;

(3)相同的行在結(jié)果中可能出現(xiàn)多次。

3.UNION DISTINCT集合運算

(1)假設(shè)Query1返回m行,Query2返回n行,Query1和Query2有相同的h行,則Query1 UNION Query2返回(m+n-h)行;

(2)UNION 會刪除重復(fù)行,所以它的結(jié)果就是集合;

(3)相同的行在結(jié)果中只出現(xiàn)一次。

(4)不能顯示指定DISTINCT字句,如果不指定ALL,則默認(rèn)使用DISTINCT。

(5)當(dāng)Query1與Query2比較某行記錄是否相等時,會認(rèn)為取值為NULL的列是相等的列。

三、INTERSECT(交集)集合運算

1.交集的文氏圖

交集:兩個集合(記為集合A和集合B)的交集是由既屬于A,也屬于B的所有元素組成的集合。

圖中陰影區(qū)域代表集合A與集合B的交集

 

 

2.INTERSECT DISTINCT集合運算

(1)假設(shè)Query1返回 m 行,Query2返回 n 行,Query1和Query2有相同的 h 行,則Query1 INTERSECT Query2返回 h 行;

(2)INTERSECT集合運算在邏輯上首先刪除兩個輸入多集中的重復(fù)行(把多集變?yōu)榧?,然后返回只在兩個集合中都出現(xiàn)的行;

(3)INTERSECT 會刪除重復(fù)行,所以它的結(jié)果就是集合;

(4)相同的行在結(jié)果中只出現(xiàn)一次。

(5)不能顯示指定DISTINCT字句,如果不指定ALL,則默認(rèn)使用DISTINCT。

(6)當(dāng)Query1與Query2比較某行記錄是否相等時,會認(rèn)為取值為NULL的列是相等的列。

(7)用內(nèi)聯(lián)接或EXISTS謂詞可以代替INTERSECT集合運算,但是必須對NULL進(jìn)行處理,否則這兩種方法對NULL值進(jìn)行比較時,比較結(jié)果都是UNKNOWN,這樣的行會被過濾掉。

3.INTERSECT ALL集合運算

(1)ANSI SQL支持帶有ALL選項的INTERSECT集合運算,但SQL Server2008現(xiàn)在還沒有實現(xiàn)這種運算。后面會提供一種用于T-SQL實現(xiàn)的替代方案;

(2)假設(shè)Query1返回 m 行,Query2返回 n 行,如果行R在Query1中出現(xiàn)了x次,在Query2中出現(xiàn)了y次,則行R應(yīng)該在INTERSECT ALL運算之后出現(xiàn)minimum(x,y)次。

下面提供用于T-SQL實現(xiàn)的INTERSECT ALL集合運算:公用表表達(dá)式 + 排名函數(shù)

 

 

結(jié)果如下:

 

 

其中UK NULL London有四個重復(fù)行,

在排序函數(shù)的OVER字句中使用 ORDER BY ( SELECT 常量> )可以告訴SQL Server不必在意行的順序。

四、EXCEPT(差集)集合運算

1.差集的文氏圖

差集:兩個集合(記為集合A和集合B)的由屬于集合A,但不屬于集合B的所有元素組成的集合。

圖中陰影區(qū)域代表集合A與集合B的差集

 

 

2.EXCEPT DISTINCT集合運算

(1)假設(shè)Query1返回 m 行,Query2返回 n 行,Query1和Query2有相同的 h 行,則Query1 INTERSECT Query2返回 m – h 行,而Query2 INTERSECT Query1 返回 n – h 行

(2)EXCEPT集合運算在邏輯上先刪除兩個輸入多集中的重復(fù)行(把多集轉(zhuǎn)變成集合),然后返回只在***個集合中出現(xiàn),在第二個集合眾不出現(xiàn)所有行。

(3)EXCEPT 會刪除重復(fù)行,所以它的結(jié)果就是集合;

(4)EXCEPT是不對稱的,差集的結(jié)果取決于兩個查詢的前后關(guān)系。

(5)相同的行在結(jié)果中只出現(xiàn)一次。

(6)不能顯示指定DISTINCT字句,如果不指定ALL,則默認(rèn)使用DISTINCT。

(7)當(dāng)Query1與Query2比較某行記錄是否相等時,會認(rèn)為取值為NULL的列是相等的列。

(8)用左外聯(lián)接或NOT EXISTS謂詞可以代替INTERSECT集合運算,但是必須對NULL進(jìn)行處理,否則這兩種方法對NULL值進(jìn)行比較時,比較結(jié)果都是UNKNOWN,這樣的行會被過濾掉。

3.EXCEPT ALL集合運算

(1)ANSI SQL支持帶有ALL選項的EXCEPT集合運算,但SQL Server2008現(xiàn)在還沒有實現(xiàn)這種運算。后面會提供一種用于T-SQL實現(xiàn)的替代方案;

(2)假設(shè)Query1返回 m 行,Query2返回 n 行,如果行R在Query1中出現(xiàn)了x次,在Query2中出現(xiàn)了y次,且x>y,則行R應(yīng)該在EXCEPT ALL運算之后出現(xiàn) x – y 次。

下面提供用于T-SQL實現(xiàn)的EXCEPT ALL集合運算:公用表表達(dá)式 + 排名函數(shù)

  1. WITH    INTERSECT_ALL 
  2.  
  3.           AS ( 
  4.  
  5.          SELECT   ROW_NUMBER() OVER ( PARTITION BY country, region, city ORDER BY ( SELECT 
  6.  
  7.                                                               0 
  8.  
  9.                                                               ) ) AS rownum , 
  10.  
  11.                         country , 
  12.  
  13.                         region , 
  14.  
  15.                         city 
  16.  
  17.                FROM     HR.Employees 
  18.  
  19.                EXCEPT 
  20.  
  21.                SELECT   ROW_NUMBER() OVER ( PARTITION BY country, region, city ORDER BY ( SELECT 
  22.  
  23.                                                               0 
  24.  
  25.                                                               ) ) AS rownum , 
  26.  
  27.                         country , 
  28.  
  29.                         region , 
  30.  
  31.                         city 
  32.  
  33.                FROM     Sales.Customers 
  34.  
  35.              ) 
  36.  
  37.     SELECT  country , 
  38.  
  39.             region , 
  40.  
  41.             city 
  42.  
  43.     FROM    INTERSECT_ALL 

 

結(jié)果如下:

 

 

五、集合運算的優(yōu)先級

1.INTERSECT>UNION=EXCEPT

2.首先計算INTERSECT,然后從左到右的出現(xiàn)順序依次處理優(yōu)先級的相同的運算。

3.可以使用圓括號控制集合運算的優(yōu)先級,它具有***的優(yōu)先級。

六、特殊處理

1.只有ORDER BY能夠直接應(yīng)用于集合運算的結(jié)果;

2.其他階段如表運算符、WHERE、GROUP BY、HAVING等,不支持直接應(yīng)用于集合運算的結(jié)果,這個時候可以使用表表達(dá)式來避開這一限制。如根據(jù)包含集合運算的查詢定義個表表達(dá)式,然后在外部查詢中對表表達(dá)式應(yīng)用任何需要的邏輯查詢處理;

3.ORDER BY字句不能直接應(yīng)用于集合運算中的單個查詢,這個時候可以TOP+ORDER BY字句+表表達(dá)式來避開這一限制。如定義一個基于該TOP查詢的表表達(dá)式,然后通過一個使用這個表表達(dá)式的外部查詢參與集合運算。

七、練習(xí)題

1.寫一個查詢,返回在2008年1月有訂單活動,而在2008年2月沒有訂單活動的客戶和雇員。

期望結(jié)果:

 

 

方案一:EXCEPT

(1)先用查詢1查詢出2008年1月份有訂單活動的客戶和雇員

(2)用查詢2查詢2008年2月份客戶的訂單活動的客戶和雇員

(3)用差集運算符查詢2008年1月有訂單活動而2008年2月沒有訂單活動的客戶和雇員

  1. SELECT  custid , 
  2.  
  3.         empid 
  4.  
  5. FROM    Sales.Orders 
  6.  
  7. WHERE   orderdate >= '20080101' 
  8.  
  9.         AND orderdate = '20080201' 
  10.  
  11.         AND orderdate 

 

方案二:NOT EXISTS

必須保證custid,empid不能為null,才能用NOT EXISTS進(jìn)行查詢,如果custid或empid其中有null值存在,則不能用NOT EXISTS進(jìn)行查詢,因為比較NULL值的結(jié)果是UNKNOWN,這樣的行用NOT EXISTS查詢返回的子查詢的行會被過濾掉,所以***的外查詢會多出NULL值的行,***查詢結(jié)果中會多出NULL值的行。

  1. SELECT  custid , 
  2.  
  3.         empid 
  4.  
  5. FROM    Sales.Orders AS O1 
  6.  
  7. WHERE   orderdate >= '20080101' 
  8.  
  9.         AND orderdate = '20080201' 
  10.  
  11.                                 AND orderdate 

 

如果我往Sales.Orders表中插入兩行數(shù)據(jù):

插入cutid=NULL,empid=1,orderdate=‘20080101’

  1. INSERT  INTO [TSQLFundamentals2008].[Sales].[Orders] 
  2.  
  3.         ( [custid] , 
  4.  
  5.           [empid] , 
  6.  
  7.           [orderdate] , 
  8.  
  9.           [requireddate] , 
  10.  
  11.           [shippeddate] , 
  12.  
  13.           [shipperid] , 
  14.  
  15.           [freight] , 
  16.  
  17.           [shipname] , 
  18.  
  19.           [shipaddress] , 
  20.  
  21.           [shipcity] , 
  22.  
  23.           [shipregion] , 
  24.  
  25.           [shippostalcode] , 
  26.  
  27.           [shipcountry] 
  28.  
  29.         ) 
  30.  
  31. VALUES  ( NULL , 
  32.  
  33.           1 , 
  34.  
  35.           '20080101' , 
  36.  
  37.           '20080101' , 
  38.  
  39.           '20080101' , 
  40.  
  41.           1 , 
  42.  
  43.           1 , 
  44.  
  45.           'A' , 
  46.  
  47.           '20080101' , 
  48.  
  49.           'A' , 
  50.  
  51.           'A' , 
  52.  
  53.           'A' , 
  54.  
  55.           'A' 
  56.  
  57.         ) 
  58.  
  59. GO 

 

插入cutid=NULL,empid=1,orderdate=‘20080201’

  1. INSERT  INTO [TSQLFundamentals2008].[Sales].[Orders] 
  2.  
  3.         ( [custid] , 
  4.  
  5.           [empid] , 
  6.  
  7.           [orderdate] , 
  8.  
  9.           [requireddate] , 
  10.  
  11.           [shippeddate] , 
  12.  
  13.           [shipperid] , 
  14.  
  15.           [freight] , 
  16.  
  17.           [shipname] , 
  18.  
  19.           [shipaddress] , 
  20.  
  21.           [shipcity] , 
  22.  
  23.           [shipregion] , 
  24.  
  25.           [shippostalcode] , 
  26.  
  27.           [shipcountry] 
  28.  
  29.         ) 
  30.  
  31. VALUES  ( NULL , 
  32.  
  33.           1 , 
  34.  
  35.           '20080201' , 
  36.  
  37.           '20080101' , 
  38.  
  39.           '20080101' , 
  40.  
  41.           1 , 
  42.  
  43.           1 , 
  44.  
  45.           'A' , 
  46.  
  47.           '20080101' , 
  48.  
  49.           'A' , 
  50.  
  51.           'A' , 
  52.  
  53.           'A' , 
  54.  
  55.           'A' 
  56.  
  57.         ) 
  58.  
  59. GO 

 

用方案一查詢出來結(jié)果為50行,會把cutid=NULL,empid=1的行過濾掉

用方案二查詢出來結(jié)果為51行,不會把cutid=NULL,empid=1的行過濾掉

用下面的方案可以解決上面的問題,需要處理cutid=NULL,或者empid=null的情況。返回50行

  1. SELECT  custid , 
  2.  
  3.         empid 
  4.  
  5. FROM    Sales.Orders AS O1 
  6.  
  7. WHERE   orderdate >= '20080101' 
  8.  
  9.         AND orderdate = '20080201' 
  10.  
  11.                             AND orderdate 

 

2.寫一個查詢,返回在2008年1月和在2008年2月都有訂單活動的客戶和雇員。

期望結(jié)果:

 

 

方案一:INTERSECT

(1)先用查詢1查詢出2008年1月份有訂單活動的客戶和雇員

(2)用查詢2查詢2008年2月份客戶的訂單活動的客戶和雇員

(3)用交集運算符查詢2008年1月和2008年2月都有訂單活動的客戶和雇員

  1. SELECT  custid , 
  2.  
  3.         empid 
  4.  
  5. FROM    Sales.Orders 
  6.  
  7. WHERE   orderdate >= '20080101' 
  8.  
  9.         AND orderdate = '20080201' 
  10.  
  11.         AND orderdate 

 

方案二:EXISTS

必須保證custid,empid不能為null,才能用EXISTS進(jìn)行查詢,如果custid或empid其中有null值存在,則不能用EXISTS進(jìn)行查詢,因為比較NULL值的結(jié)果是UNKNOWN,這樣的行用EXISTS查詢返回的子查詢的行會被過濾掉,所以***的外查詢會少NULL值的行,***查詢結(jié)果中會少NULL值的行。

  1. SELECT  custid , 
  2.  
  3.         empid 
  4.  
  5. FROM    Sales.Orders AS O1 
  6.  
  7. WHERE   orderdate >= '20080101' 
  8.  
  9.         AND orderdate = '20080201' 
  10.  
  11.                             AND orderdate 

 

如果我往Sales.Orders表中插入兩行數(shù)據(jù):

插入cutid=NULL,empid=1,orderdate=’20080101′

插入cutid=NULL,empid=1,orderdate=’20080201′

用方案一查詢出來結(jié)果為6行,不會把cutid=NULL,empid=1的行過濾掉

用方案二查詢出來結(jié)果為5行,會把cutid=NULL,empid=1的行過濾掉

用下面的方案可以解決上面的問題,需要處理cutid=NULL,或者empid=null的情況。返回6行。

  1. SELECT  custid , 
  2.  
  3.         empid 
  4.  
  5. FROM    Sales.Orders AS O1 
  6.  
  7. WHERE   orderdate >= '20080101' 
  8.  
  9.         AND orderdate = '20080201' 
  10.  
  11.                             AND orderdate 

 

3.寫一個查詢,返回在2008年1月和在2008年2月都有訂單活動,而在2007年沒有訂單活動的客戶和雇員

期望結(jié)果:

 

 

方案一:INTERSECT + EXCEPT

  1. SELECT  custid , 
  2.  
  3.         empid 
  4.  
  5. FROM    Sales.Orders 
  6.  
  7. WHERE   orderdate >= '20080101' 
  8.  
  9.         AND orderdate = '20080201' 
  10.  
  11.         AND orderdate = '20070101' 
  12.  
  13.         AND orderdate 

 

方案二:EXISTS + NOT EXISTS

  1. SELECT  custid , 
  2.  
  3.         empid 
  4.  
  5. FROM    Sales.Orders AS O1 
  6.  
  7. WHERE   orderdate >= '20080101' 
  8.  
  9.         AND orderdate = '20080201' 
  10.  
  11.                             AND orderdate = '20070101' 
  12.  
  13.                                 AND orderdate 

 

責(zé)任編輯:龐桂玉 來源: 數(shù)據(jù)庫開發(fā)
相關(guān)推薦

2010-09-07 11:53:00

SQL語句

2010-04-29 14:06:40

Oracle SQL

2010-04-13 14:36:17

Oracle性能檢測

2020-09-30 14:24:58

PythonSet對象

2010-10-14 09:32:52

SQL Server

2023-03-29 09:22:03

SQLWhere語句

2019-10-22 13:34:06

SQL數(shù)據(jù)庫語句解讀

2010-02-25 10:04:33

MySQL執(zhí)行SQL語

2009-03-04 13:10:41

SQL語句INSERTDELETE

2011-05-19 11:00:44

2011-08-15 14:29:52

SQL Server數(shù)事務(wù)

2010-09-03 15:47:40

SQL語句鎖定

2010-09-03 14:39:15

SQLSELECT語句

2023-03-30 09:10:06

SQLSELECTFROM

2010-07-08 13:26:02

SQL Server

2010-09-03 11:25:58

SQL刪除

2010-09-17 16:03:17

鎖定SQL表

2010-11-11 11:37:22

SQL SELECT語

2010-09-08 16:26:26

SQL循環(huán)語句

2024-07-09 08:49:56

點贊
收藏

51CTO技術(shù)棧公眾號

主站蜘蛛池模板: 一二三四在线视频观看社区 | 精品国产区| 国产在线麻豆精品入口 | 亚洲精品一区二区三区蜜桃久 | 久草新在线 | 欧美午夜精品 | 激情亚洲 | 亚洲欧美一区二区三区国产精品 | 成人精品视频在线 | 免费人成在线观看网站 | 亚洲字幕在线观看 | 国产九九精品视频 | 一级黄大片| 精品国产视频在线观看 | 欧美一区二区三区小说 | 男女羞羞视频在线观看 | h视频在线观看免费 | 美女视频三区 | 日韩毛片| 成人欧美一区二区三区黑人孕妇 | 日本h片在线观看 | 色视频一区二区 | 成人欧美一区二区三区 | 欧美日韩高清一区 | 日韩一区二区福利视频 | 九九热在线精品视频 | 福利影院在线看 | 2018天天干天天操 | 国产欧美精品一区二区 | 日韩欧美在线视频一区 | 九九热这里只有精品在线观看 | 亚洲一区二区三区在线 | 欧美日韩国产免费 | 国产婷婷| 欧美一级欧美三级在线观看 | 97精品国产97久久久久久免费 | 国产一级片一区二区三区 | 91天堂| 精品啪啪| 亚洲日韩中文字幕一区 | 亚洲www. |