2、第二個特性----以集合為單位進行操作
在我們以往程式導向語言不同，SQL是一門面向集合的一門語言。由於習慣了程式導向的思考方式，導致我們在使用SQL時往往也陷入之前的思維定式。
我們現在分別建立customers表和orders表
customers orders
我們最直觀看到資料庫的資料組織方式是通過 檢視查詢出來，就像上面兩張圖我們所看到的一樣。
而資料庫儲存資料其實是如下兩張圖
customers和orders的集合
想了下怎麼講解這個以集合為單位進行操作最後還得通過案例進行說明，而最能體現這個特性就在於對 Having 的使用。
例一：求眾數
以程式導向的思路分析這道題是如下步驟：
1）建立一個MAP<item,num>集合,item是數字，num是出現的次數。
2）對輸入的陣列集合作遍歷，並判斷MAP中是否存在該數字，若存在則將num+1，若不存在則將數字放入MAP中。
3）對MAP中num屬性提取出最大值。
4）通過最大num值在MAP中找到相應的數字。
而在面向集合中解題思路應該是這樣的：
1）對陣列列表中每一個數字進行分組
2）計算每一組數字的個數 並 要大於任何一組數字的個數 ，該數字即為該陣列的眾數。
--求眾數SQL語句（1）:使用謂詞 SELECT income,COUNT(*) AS cnt FROM Graduates GROUP BY income HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM Graduates GROUP BY income) --求眾數SQL語句（2）：使用極值函式 SELECT income,COUNT(*) AS cnt FROM Graduates GROUP BY income Having COUNT(*) >= ( SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM Graduates GROUP BY income) TMP);
分組集合操作
例二：求中位數
程式導向解析：
1）對集合進行大小排序。
2）如果是奇數則取集合中間一個數字為中位數，如果是偶數則取中間兩個數字的平均值為中位數。
面向集合解析：
1）將集合按大小分為上半部分和下半部分兩個子集
2）讓兩個子集同時擁有的元素取平均值就為中位數
SELECT AVG(DISTINCT income) FROM (SELECT a.income FROM Graduates a,Graduates b GROUP BY a.income HAVING SUM(CASE WHEN a.income >= b.income THEN 1 ELSE 0 END) >= COUNT(*)/2 AND SUM(CASE WHEN a.income <= b.income THEN 1 ELSE 0 END) >= COUNT(*)/2 )TMP
例三：查詢所有學生都提交了報告的學院
程式導向解析：
1）對陣列集合進行迴圈遍歷篩選出提交日期為空的學院
2）對第一步產生的結果再原陣列集合作差集，篩選出所有提交報告的學院
面向集合解析：
1）對集合按照學院分組，並統計每個學院已提交報告的數量
2）對第一步產生結果與原集合按學院分組後的數量進行比對，若數量一致則為已提交所有報告的學院
SELECT COUNT(*) FROM Students GROUP BY COUNT(*) = COUNT(sbmt_date)
例四：關係除法運算
Items ShopItems
1.查詢包含在Items所有商品的商店
程式導向解析：
1)在 shopitems 表中按照店鋪作陣列拆分
2)對拆分出來的陣列中所售商品按照Items進行遍歷比對，若檢測其中任意一個Items商品不在該陣列中則將其篩選出。
面向集合解析：
1) 將shopitems按照店鋪分組 並 對 shopitems 與 items 按照 item 做關聯
2) 統計關聯後的分組結果數量是否和Items表中商品數量是否一致
/*查詢店鋪中包含表Items中所有商品的店鋪*/ SELECT shop FROM ShopItems a,Items b WHERE a.item = b.item GROUP BY shop HAVING COUNT(*) = (SELECT COUNT(*) FROM Items)
2.精確關係除法
第一個問題按照上述邏輯會篩選出東京和仙台，而仙台還包含有“窗簾”這一項在Items是不存在的，這樣的查詢稱之為 帶餘除法
現在我們要從shopitems篩選出與items完全重合店鋪資訊，就是隻需要篩選出東京。這個問題稱之為 精確關係除法。
1) 將shopitems按照店鋪分組 並 對 shopitems 與 items 按照 item 做外關聯，確保shopitems中的元素不會缺失
2）讓匹配到的 items 中的元素與 Items數量作比較是否一致
3）比較店鋪中所有商品與匹配到的商品數量是否一致
通過（2）能夠確保 在Items中存在的商品都存在於店鋪中
通過（3）能夠確保 店鋪中的商品數量是與匹配到的商品數量是一致的
在有了（2）的前提下，若滿足（3）的條件，則說明店鋪中商品是和商品表裡的商品是一一對應的。
/*精確關係除法*/ SELECT shop FROM ShopItems a LEFT JOIN Items b ON a.item = b.item GROUP BY shop HAVING COUNT(b.item) = (SELECT COUNT(*) FROM Items) AND COUNT(a.item) = COUNT(b.item)
3.這裡我們再解析下什麼是關係除法
首先這裡我們給出它的數學定義：
然後我們再按照上述定義來解析這兩道題
1）shopitems表中存在兩個屬性，shop屬性和Item屬性；items表中存在item一個屬性；他們的共同屬性為item
2）Items在shopitems上的投影為
2）shopitems表分量shop屬性的象集為：
象集1 象集2 象集3
3）那麼我們能看3個象集中包含關係Items在shopitems上的投影的是 象集1和象集2
最後我們再通過下面兩張圖就更清楚，帶餘除法和精確除法的關係了
帶餘除法 精確除法
這就是關係除法的原理，那為什麼這樣的運算稱之為除法運算呢？
答：因為這樣會產生一個結果（商），通過再與我們的除數進行笛卡爾積運算能夠得到被除數的子集或者被除數本身。由於笛卡爾積運算我們稱之為乘法運算，那麼作為它的逆向運算就為除法運算了。
例五：行轉列 製作交叉表
那麼本題還是應該使用集合的思想來解題，我們將原資料按下圖整理
我們按人名對集合進行分為5組後，Group by name 的 name的值 就能作為我們的側邊欄，而表頭則需要我們自己作定義，根據每個分組裡面的值進行判斷。
舉例：赤井這個分組我們根據已知表頭中三個課程進行判斷，如果相同則標記為‘O’;不同則標記為‘X’。
/*課程記錄一覽表*/ SELECT a.name, MAX(CASE WHEN course = 'SQL入門' THEN '○' ELSE '×' END) AS 'SQL入門', MAX(CASE WHEN course = 'UNIX基礎' THEN '○' ELSE '×' END) AS 'UNIX基礎', MAX(CASE WHEN course = 'Java中級' THEN '○' ELSE '×' END) AS 'Java中級' FROM Courses a GROUP BY a.name
例六：移動累計值
Accounts
1）求截止到某個處理日期的處理金額的累計值，實際上就是求截止到那個時間點的賬戶餘額
本題還是得用集合的思想解決，但是現在日期都不同，那麼現在以什麼作為分組呢？
答案是我們需要作一次自關聯，讓其產生一個每個日期小於等於它本身的日期集合，然後對這個集合統一作累計計算。
/* 求累計值 */ SELECT prc_date, prc_amt, (SELECT SUM(prc_amt) FROM Accounts b WHERE a.`prc_date` >= b.prc_date) AS onhand_amt FROM Accounts a
2）我們考慮一下如何以3次處理為單位求累計值，即移動累計值。所謂移動，指的是將累計的資料行數固定（本例中為3行），一行一行地偏移，如下表所示。
還是根據剛才所講按每個日期和每個比他本身小的日期作分組，不過這次還要加上在區間範圍不超過3的條件
/*求移動累計值*/ SELECT a.`prc_date`,SUM(b.`prc_amt`) FROM Accounts a,Accounts b WHERE a.`prc_date` >= b.`prc_date` AND (SELECT COUNT(*) FROM Accounts c WHERE c.`prc_date` BETWEEN b.`prc_date` AND a.`prc_date`)<=3 GROUP BY a.`prc_date`
對於區間不滿三行的資料則不輸出
/*不滿三行不作輸出*/ SELECT a.`prc_date`,SUM(b.`prc_amt`) FROM Accounts a,Accounts b WHERE a.`prc_date` >= b.`prc_date` AND (SELECT COUNT(*) FROM Accounts c WHERE c.`prc_date` BETWEEN b.`prc_date` AND a.`prc_date`)<=3 GROUP BY a.`prc_date` HAVING COUNT(*) = 3
例七：查詢重疊的時間區間
Reservations
本題作自關聯，為每一個時間段預先製造一個分組集合，在集合內先完成條件篩選。
/**查詢住宿重疊時間**/ SELECT reserver, astart, aend FROM ( SELECT a.reserver,a.start_date AS astart,a.end_date AS aend,b.start_date AS bstart,b.end_date AS bend FROM Reservations a,Reservations b WHERE a.reserver <> b.reserver )tmp WHERE bstart BETWEEN astart AND aend OR bend BETWEEN astart AND aend OR bstart BETWEEN astart AND aend AND bend BETWEEN astart AND aend
例8：查詢兩個集合是否相等
tbl_A tbl_B
如何比較這兩個集合元素是否是相等的呢
1）那我們可以使用 A+B=A=B 的條件來判斷這樣的場景
/*判斷集合是否相等*/ SELECT COUNT(*) FROM ( SELECT * FROM tlb_A UNION SELECT * FROM tlb_B )TMP
2）第一種解法就要先確定A和B的行數，那現在想一想能不能直接對A、B進行比較呢？
那可以利用兩個集合的並集和差集來判定其相等性。如果用SQL語言描述，那就是“如果A UNION B = A INTERSECT B，則集合A和集合B相等”。
(A ∪ B ) = (A ∩ B) ⇔ (A = B)
/*判斷集合是否相等*/ SELECT CASE WHEN COUNT(*) = 0 THEN '相等' ELSE '不相等' END AS result FROM ((SELECT * FROM tbl_A UNION SELECT * FROM tbl_B) EXCEPT (SELECT * FROM tbl_A INTERSECT SELECT * FROM tbl_B) ))TMP;
例9：尋找相等子集
SupParts
問題：找出經營的零件在種類數和種類上都完全相同的供應商組合。
SQL並沒有提供任何用於檢查集合的包含關係或者相等性的謂詞。IN 謂詞只能用來檢查元素是否屬於某個集合，而不能檢查集合是否是某個集合的子集。
那我們就先給出答案
/*尋找相等子集*/ SELECT a.`sup`,b.`sup` FROM SupParts a,SupParts b WHERE a.`part` = b.`part` AND a.`sup` < b.`sup` GROUP BY a.`sup`,b.`sup` HAVING COUNT(*) = (SELECT COUNT(*) FROM SupParts c WHERE a.`sup` = c.`sup`) AND COUNT(*) = (SELECT COUNT(*) FROM SupParts d WHERE b.`sup` = d.`sup`)
大家對此是不是比較熟悉
其實這個思路和例四很像，只不過例四是隻檢查一個集合是否要和另一個集合相等。而本題是需要同時檢查兩個集合是否都和第三個集合相等。這樣保證集合A和集合B關聯後記錄不會丟失，因為C和D是完整的記錄而且都是自己本身。