1、數據庫基礎與實踐技術（SQL Server 2008）第七章第7章 高級查詢7.1 CASE函數7.2 子查詢7.3 查詢結果的并、交、差運算7.4 其他一些查詢功能 7.4.1 開窗函數 7.4.2 公用表表達式 7.4.3 Merge語句2/1317.1 CASE函數是一種多分支函數，可以根據條件列表的值返回多個可能結果中的一個。可用在任何允許使用表達式的地方。不是一個完整的T-SQL語句，不能單獨執行。3/1311. 簡單CASE函數CASE input_expression WHEN when_expression THEN result_expression .n ELSE else

2、_result_expression END4/131示例【例1】查詢選了Java課程的學生的學號、姓名、所在系和成績，并對所在系進行如下處理：“計算機系”：顯示“CS”；“信息管理系”：顯示“IM”；“通信工程系”：顯示“COM”。 SELECT s.Sno 學號,Sname 姓名, CASE Dept WHEN 計算機系 THEN CS WHEN 信息管理系 THEN IM WHEN 通信工程系 THEN COM END AS 所在系,Grade 成績 FROM Student s join SC ON s.Sno = SC.Sno JOIN Course c ON c.Cno = SC.

3、Cno WHERE Cname = JAVA5/1312搜索CASE函數CASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expression END6/131示例【例2】查詢“C001”課程的考試情況，列出學號和成績，對成績進行如下處理如果成績大于等于90，則在查詢結果中顯示“優”；如果成績在80到89分之間，則在查詢結果中顯示“良”；如果成績在70到79分之間，則在查詢結果中顯示“中”；如果成績在60到69分之間，則在查詢結果中顯示“及格”；如果成績小于60分，則在查詢結果中顯示“不及格”。7/1

4、31SELECT Sno, CASE WHEN Grade = 90 THEN 優 WHEN Grade between 80 and 89 THEN 良 WHEN Grade between 70 and 79 THEN 中 WHEN Grade between 60 and 69 THEN 及格 WHEN Grade 4 THEN 多 WHEN COUNT(SC.Cno) BETWEEN 2 AND 4 THEN 一般 WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN 少 WHEN COUNT(SC.Cno) = 0 THEN 未選END AS 選課情況FRO

5、M Student S LEFT JOIN SC ON S.Sno = SC.SnoWHERE Dept = 計算機系 GROUP BY S.SnoORDER BY COUNT(SC.Cno) DESC7.1.2 CASE 函數應用示例【例4】修改全體學生的JAVA考試成績，修改規則如下：對通信工程系學生，成績加10分；對信息管理系學生，成績加5分；對其他系學生，成績不變。UPDATE SC SET Grade = Grade + CASE Dept WHEN 通信工程系 THEN 10 WHEN 信息管理系 THEN 5 ELSE 0 END FROM Student S JOIN SC O

6、N S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = JAVA12/1317.1.2 CASE 函數應用示例7.2 子查詢7.2.1 嵌套子查詢(不相關子查詢）7.2.2 相關子查詢7.2.3 其他形式的子查詢13/131【說明】1.如果一個select語句嵌套在另一個select、insert、update或delete語句中，則稱為子查詢。2.嵌套子查詢：內層查詢中不關聯外層查詢的子查詢。3.相關子查詢：內層查詢利用外層查詢提供的信息執行。4.子查詢語句可以出現在任何能夠用表達式的地方。7.2 子查詢（subquery

7、）1.查詢學生姓名、所在系和該學生選的課程門數。SELECT Sname,Dept, (SELECT COUNT(*) FROM SC WHERE Sno = Student.Sno ) AS CountCno FROM Student15/1317.2 子查詢（subquery）2.查詢課程名、開課學期及選該門課的學生人數、平均成績,不包括沒人選的課程。SELECT Cname AS 課程名,semester AS 開課學期, ( SELECT COUNT(*) FROM SC WHERE Cno = Course.Cno ) AS 選課人數, ( SELECT AVG(Grade) FRO

8、M SC WHERE Cno = Course.Cno) AS 平均成績 FROM Course WHERE Cno IN (SELECT Cno FROM SC)16/1317.2 子查詢（subquery）【例5】查詢課程號、課程名、開課學期、學分以及該學期開設課程的總學分、平均學分、最低學分和最高學分。 SELECT Cno, Cname, Semester,Credit, SUM(Credit) OVER(PARTITION BY Semester) AS Total, AVG(Credit) OVER(PARTITION BY Semester) AS Avg, MIN(Credit

9、) OVER(PARTITION BY Semester) AS Min, MAX(Credit) OVER(PARTITION BY Semester) AS Max FROM CourseOVER子句與聚合函數結合使用EXISTS形式的子查詢EXISTS代表存在量詞。WHERE NOT EXISTS（子查詢）不返回查詢的數據，只產生邏輯真值和假值。EXISTS：當子查詢中有滿足條件的數據時，返回真值，否則返回假值。NOT EXISTS：當子查詢中有滿足條件的數據時，返回假值；否則返回真值。18/131【例6.1】查詢選了JAVA課程的學生姓名和所在系。SELECT Sname, Dept

10、FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno=Student.Sno AND EXISTS ( SELECT * FROM Course WHERE Cno = SC.Cno AND Cname = JAVA)【例6.2】查詢沒有選修JAVA課程的學生姓名和所在系。19/131EXISTS形式的子查詢not【例7】查詢至少選了全部課程的學生的學號、姓名和所在系。該查詢的關系代數表達式為： Sno,Sname,Sdept（Student）（SNO,CNO(SC) cno（ourse）SQL語言中沒有提供除運算，而且，除運算也不能用如

11、ALL、=ALL、=ALL等量化的謂詞形式構造。20EXISTS形式的子查詢分析（1）構造反例：設有一門課程是s.sno沒有選的（2）將步驟1構造的反例表達為搜索條件；（3）建立表示這類反例不存在的搜索條件；（4）建立完整的SELECT語句。 21最終語句SELECT s.Sno, Sname, Sdept FROM Student s WHERE NOT EXISTS( SELECT * FROM Course c WHERE NOT EXISTS( SELECT * FROM SC x WHERE x. Cno = c.Cno and x.Sno = s.Sno)22【例8】查詢至少選了“

12、0811102”學生所選的全部課程的學生的學號和所選的課程號（1）構造反例：有一個“0811102”學生選的課程是?.sno沒有選的。 23EXISTS形式的子查詢示例（續）（4）建立完整的SELECT語句SELECT Sno, Cno FROM SC s1 WHERE NOT EXISTS( SELECT * FROM SC x WHERE Sno = 0811102 and NOT EXISTS( select * from SC y where y.Cno = x.Cno and y.Sno = s1.Sno) and Sno != 0811102去掉0811102本人24派生表也稱為內

13、聯視圖,是將子查詢做為一個表來處理。由子查詢產生的新表就稱之為“派生表”，這很類似于臨時表。select * from (select * from table) as temp25/131示例【例9】查詢至少選了C001和C002兩門課程的學生學號。SELECT T1.Sno FROM (SELECT * FROM SC WHERE Cno = C001) AS T1 JOIN (SELECT * FROM SC WHERE Cno = c002) AS T2 ON T1.Sno=T2.Sno26/1317.3 查詢結果的并、交、差運算SELECT語句的查詢結果是元組的集合，所以多個SELE

14、CT語句的結果可進行集合操作。集合操作主要包括：UNION（并）、INSTERSECT（交）EXCEPT（差）27/7627/131一些說明所有的SELECT語句列表中列的個數必須相同，而且對應列的語義應該相同。各SELECT語句中每個列的數據類型必須兼容。合并后的結果采用第一個SELECT語句的列標題。如果要對查詢的結果進行排序，則ORDER BY子句寫在最后一個查詢語句之后。28/7628/1317.4 其他一些查詢功能7.4.1 開窗函數7.4.2 公用表表達式7.4.3 Merge語句29/1317.4.1 開窗函數開窗函數是指可以用于“分區”或“分組”計算的函數。SQL SERVER

15、提供排名開窗函數和聚合開窗函數。結合over子句使用：OVER (PARTITION BY value_expression , . n )30/131【例1】 查詢課程號、課程名、開課學期、學分以及該學期開設課程的總學分、平均學分、最低學分和最高學分。 SELECT Cno, Cname, Semester,Credit, SUM(Credit) OVER(PARTITION BY Semester) AS Total, AVG(Credit) OVER(PARTITION BY Semester) AS Avg, MIN(Credit) OVER(PARTITION BY Semester

16、) AS Min, MAX(Credit) OVER(PARTITION BY Semester) AS Max FROM Course31/1311. 將OVER子句與聚合函數結合使用【例2】設有表MyOrderDetail：32/1311. 將OVER子句與聚合函數結合使用【例2】查詢訂單號、產品號、訂購數量、每個訂單的總訂購數量以及每個產品的訂購數量占該訂單總訂購數量的百分比。(MySimpleDB)SELECT OrderID 訂單號, ProductID 產品號, OrderQty 訂購數量, SUM(OrderQty) OVER(PARTITION BY OrderID) 總計,

17、CAST(1.0*OrderQty/SUM(OrderQty) OVER(PARTITION BY OrderID) *100 AS DECIMAL(5,2) AS 所占百分比 FROM MyOrderDetail33/1311. 將OVER子句與聚合函數結合使用34/1311. 將OVER子句與聚合函數結合使用【練習】查詢學號、姓名、性別、所在系，以及該系的學生總人數、男女生人數及男女生百分比。1. 將OVER子句與聚合函數結合使用2.over子句與排名函數一起使用排名函數為分區中的每一行返回一個排名值。SQL Server提供了4個排名函數：RANKDENSE_RANKROW_NUMBER

18、NTILE36/131RANK()函數RANK()OVER( , n )RANK()函數返回結果集的分區內每行的排名。每個分區內行的排名從1開始。排序時有相同值的行具有相同排名。37/131查詢訂單號、產品號、訂購數量以及每個產品在每個訂單中的訂購數量排名。38/131RANK()函數【例3】查詢訂單號、產品號、訂購數量以及每個產品在每個訂單中的訂購數量排名。(MySimpleDB)SELECT OrderID, ProductID, OrderQty, RANK() OVER (PARTITION BY OrderID ORDER BY OrderQty DESC) AS RANKFROM

19、MyOrderDetailORDER BY OrderID39/131RANK()函數DENSE_RANK()函數DENSE_RANK()函數與RANK()函數的作用基本一樣，使用方法也一樣。DENSE_RANK()OVER( , n )唯一區別是DENSE_RANK()函數的排名中間沒有任何間斷，即該函數將返回的是一個連續的整數值。40/13141/131DENSE_RANK()函數ROW_NUMBER()函數返回結果集分區內行的序列號，每個分區的第一行從1開始。 ROW_NUMBER ( ) OVER ( )與Rank()函數的區別是生成的序列號不重復。42/131【例4】 查詢“計算機文

20、化學”的考試情況，列出學號、姓名、所在系、考試成績及成績排名。SELECT S.Sno, Sname, Dept, Grade, ROW_NUMBER() OVER(ORDER BY Grade DESC) AS NumberFROM Student S JOIN SC ON S.Sno = SC.SnoJOIN Course C ON C.Cno = SC.CnoWHERE C.Cname = 計算機文化學43/131ROW_NUMBER()函數【練習】查詢學生學號、選的課程的課程號、考試成績以及考試成績在該門課程中排名。2.over子句與排名函數一起使用7.4.2 公用表表達式公用表表達式

21、（CTE，Common Table Expression)將查詢語句產生的結果集指定一個臨時命名的名字，這些命名的結果集就稱為公用表表達式。CTE可以在SELECT、INSERT、UPDATE、DELETE等語句中被多次引用。45/131公用表表達式的語法格式WITH ,.n AS ( SELECT 語句 )其中，:= expression_name ( column_name ,.n ) 46/131示例【例5】 定義一個統計每門課程的選課人數的簡單CTE，并利用該CTE查詢課程號和選課人數。 WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT

22、(*) FROM SC GROUP BY Cno ) SELECT Cno, Counts FROM CnoCount 47/131示例【例6】利用CTE查詢選課人數超過2人的課程。WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT(*) FROM SC GROUP BY Cno )SELECT Cno, Counts FROM CnoCount WHERE Counts 2 ORDER BY Counts48/1317.4.3 Merge語句MERGE語句是SQL Server 2008新增加的數據操作語句。該語句的功能是根據源表對目標表執行插

23、入、更新或刪除操作。最典型的應用就是進行兩個表的同步。MERGE語句最后的分號是不能省略的!49/1311.MERGE語句格式MERGE 目標表USING 源表ON 匹配條件WHEN MATCHED THEN 語句WHEN NOT MATCHED by target|by source THEN 語句;50/131示例【例7】設有Product及ProductNew兩個表，現希望將Product表中的數據同步到ProductNew表中。 CREATE TABLE Product ( ProductID varchar(7) PRIMARY KEY, ProductName varchar(50

24、) NOT NULL, Price decimal(6,1) DEFAULT 0 ) CREATE TABLE ProductNew ( ProductID varchar(7) PRIMARY KEY, ProductName varchar(50) NOT NULL, Price decimal(6,1) DEFAULT 0 )51/131插入操作在Product表中插入如下數據： INSERT INTO Product Values (4100037,優盤,50), (4100038,鼠標,30), (4100039,鍵盤,100)52/131修改Product表中“4100037”產品

25、的價格為55。 UPDATE Product SET Price=55 WHERE ProductID = 4100037從Product表中刪除“410037”號產品。 DELETE FROM Product WHERE ProductID = 4100037修改和刪除操作同步操作對ProductNew表同步以上操作：MERGE ProductNew AS d USING Product AS s ON s.ProductID = d.ProductID WHEN NOT MATCHED by target THEN INSERT( ProductID,ProductName,Price)

26、VALUES(s.ProductID,s.ProductName,s.Price) -WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN MATCHED THEN UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;54/131第7章 高級查詢7.1 CASE函數7.2 子查詢7.3 查詢結果的并、交、差運算7.4 其他一些查詢功能 7.4.1 開窗函數 7.4.2 公用表表達式 7.4.3 Merge語句55/1311查詢選了“Java”課程的學生學號、姓名和JAVA成績。2.統計選了Ja

27、va課程的這些學生的選課門數和平均成績。3.查詢選了“C001”號課程且成績高于此課程的平均成績的學生的學號和成績。4. 查詢沒選“C001”號課程的學生姓名和所在系。5.將學分最低的課程的學分加2分。6.查詢每門課程考試成績最高的兩個學生的學號以及相應的課程號和成績。不包括沒考試的課程。7.查詢有最高學分超過本學期平均學分1.5倍的學期。8.查詢所學每一門課程成績均不低于該課程平均成績的學生姓名及相應課程號。 9.查詢學號、姓名、性別、所在系，以及該系的學生總人數、男女生人數及男女生百分比。10刪除JAVA考試成績最低的學生的JAVA修課記錄。56/131作 業2.用MERGE語句實現匯總數

28、據同步假設為了做月報表的需要，創建了一個月銷售匯總表。希望每日將新的銷售記錄添加到每月匯總表中。在每個月的第1天晚上，只需將銷售記錄插入到銷售匯總表中即可。但從第2天晚上開始情況就不一樣了，對于之前沒有銷售記錄的數據，只需將該數據插入到銷售匯總表中；對于之前有銷售記錄的數據，則需要更新該商品的匯總數據57/131示例（續1）本示例用MySimpleDB數據庫中的Sales.SalesOrderHeader和Sales.SalesOrderDetail表中的數據來同步銷售匯總數據。在MySimpleDB數據庫中創建銷售匯總表：CREATE TABLE Sales.MonthlyRollup( Y

29、ear smallint NOT NULL, Month tinyint NOT NULL, ProductID int NOT NULL REFERENCES Production.Product (ProductID), QtySold int NOT NULL, PRIMARY KEY(Year,Month,ProductID)58/131示例（續2）設只對2003年8月的數據進行匯總，從該月的第1天開始。構建MERGE語句，產生2003年8月1日的銷售匯總數據。MERGE Sales.MonthlyRollup AS smrUSING( SELECT soh.OrderDate, so

30、d.ProductID, SUM(sod.OrderQty) AS QtySold FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderIDWHERE soh.OrderDate = 2003-08-01 GROUP BY soh.OrderDate, sod.ProductID) AS sON (s.ProductID = smr.ProductID)WHEN MATCHED THEN UPDATE SET smr.QtySold = smr.Q

31、tySold + s.QtySoldWHEN NOT MATCHED THEN INSERT (Year, Month, ProductID, QtySold) VALUES (DATEPART(yy, s.OrderDate), DATEPART(m, s.OrderDate), s.ProductID, s.QtySold);59/131示例（續3）繼續查看該月第2天的總結果。更新日期，繼續運行下述代碼（仿真在該月第2天運行）MERGE Sales.MonthlyRollup AS smrUSING ( SELECT soh.OrderDate, sod.ProductID, SUM(so

32、d.OrderQty) AS QtySold FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderIDWHERE soh.OrderDate = 2003-08-02 GROUP BY soh.OrderDate, sod.ProductID) AS sON (s.ProductID = smr.ProductID)WHEN MATCHED THEN UPDATE SET smr.QtySold = smr.QtySold + s.QtySoldW

33、HEN NOT MATCHED THEN INSERT (Year, Month, ProductID, QtySold) VALUES (DATEPART(yy, s.OrderDate), DATEPART(m, s.OrderDate), s.ProductID, s.QtySold);60/1317.4.2 公用表表達式公用表表達式（CTE，Common Table Expression)將查詢語句產生的結果集指定一個臨時命名的名字，這些命名的結果集就稱為公用表表達式。命名好公用表表達式后就可以在SELECT、INSERT、UPDATE、DELETE等語句中被多次引用。公用表表達式還可

34、以包括對自身的引用，這種表達式稱為遞歸公用表表達式。61/131公用表表達式的語法格式WITH ,.n := expression_name ( column_name ,.n ) AS ( SELECT 語句 )62/131示例例9 定義一個統計每門課程的選課人數的簡單CTE，并利用該CTE查詢課程號和選課人數。 WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT(*) FROM SC GROUP BY Cno ) SELECT Cno, Counts FROM CnoCount ORDER BY Counts63/131例10 使用公用表表達

35、式來限制返回結果。改進例9的查詢，定義一個統計每門課程的選課人數的CTE，并利用該CTE查詢選課人數超過2人的課程。WITH CnoCount(Cno, Counts) AS ( SELECT Cno, COUNT(*) FROM SC GROUP BY Cno )SELECT Cno, Counts FROM CnoCount WHERE Counts 2 ORDER BY Counts64/131Employees表及數據示例65/131示例首先建立兩個包含職工全部信息的CTE，然后利用這兩個CTE查詢每個職工信息及上級領導信息。WITH Emp1 AS (SELECT * FROM Employees ), Emp2 AS (SELECT * FROM Employees )SELECT * FROM Emp1 JOIN Emp2 ON