2013年5月8日 星期三

合併查詢結果

在早期的 SQL Server 版本中就有提供 UNION 運算子,用以結合多個查詢結果。 直到 SQL2005 才又加入了 EXCEPTINTERSECT 運算子,以取得多個查詢結果的差集和交集。

UNION

UNION 運算子:

  • 用來結合多個 SELECT 取得的集合集。
  • 要結合的結果集,必須欄位數相同,且對應欄位的資料型別必須相容。
  • 結合後的結果集,欄位名稱是由第一個結果集的欄位名稱決定。
SELECT CONVERT(Char(8),Employees.EmployeeID) FROM Employees
UNION
SELECT Customers.CustomerID FROM Customers

其它注意事項:

  • 結合後的結果集,其欄位名稱是由第一個結果集的欄位名稱決定。
  • ORDER BY 必須置於最後一個 SELECT 陳述式之後。
  • 要撰選資料,要在每一個 SELECT 陳述式之中加入 WHERE 子句。
  • 使用 ALL 關鍵子可以回傳包含重複的列。
SELECT CONVERT(Char(8),Employees.EmployeeID) as AA FROM Employees WHERE EmployeeID>9
UNION ALL
SELECT Customers.CustomerID as BB FROM Customers WHERE CustomerID like 'A%'
ORDER BY AA desc

SELECT OrderID, ProductID FROM OrderDetails WHERE OrderID=10285 OR OrderID=10294

SELECT ProductID FROM OrderDetails WHERE OrderID=10285
UNION
SELECT ProductID FROM OrderDetails WHERE OrderID=10294

SELECT ProductID FROM OrderDetails WHERE OrderID=10285
UNION ALL
SELECT ProductID FROM OrderDetails WHERE OrderID=10294

EXCEPT and INTERSECT

You use the EXCEPT and INTERSECT operators to perform semi-joins and anti-joins.

  • semi-joins:(反聯結,交集)returns rows from the first input if there is as least one matching row in the second input.
  • anti-joins:(半聯結,差集)returns rows from the first input if there are no matching rows in the second input.

--Except
SELECT ProductID FROM OrderDetails WHERE OrderID=10285
Except
SELECT ProductID FROM OrderDetails WHERE OrderID=10294

--Intersect
SELECT ProductID FROM OrderDetails WHERE OrderID=10285
Intersect
SELECT ProductID FROM OrderDetails WHERE OrderID=10294

APPLY

APPLY 運算子主要用來將一個子查詢結果和一個資料表值函式(table-valued funtion)做 JOIN。 運算式會將每一筆查詢結果帶入資料表值函式,然後將子查詢結果會當成 left input ,資料表值函式則當成 right input ,再執行結合。

APPLY 運算子有二種用法: CROSS APPLYOUTER APPLY 。這二個都是從 SQL2005 才提供的語法。若要使用 APPLY,資料庫相容性層級至少必須是 90。

  • CROSS APPLY :相當於使用 Inner Join 與資料表值函式(table-value function)執行 JOIN 查詢。
  • OUTER APPLY :相當於使用 Left Outter Join 與資料表值函式(table-value function)執行 JOIN 查詢。

建立 table-value 函式

CREATE FUNCTION funOrderAmount(@OrderID int)
RETURNS TABLE
RETURN
(
	SELECT O.OrderID, SUM(UnitPrice*Quantity) as Amount
	FROM Orders O
	INNER JOIN OrderDetails OD ON O.OrderID=OD.OrderID
	WHERE O.OrderID=@OrderID
	GROUP BY O.OrderID
)

使用 CROSS APPLY 取得所有訂單的總金額

SELECT O.OrderID, O.CustomerID, OA.Amount
FROM Orders O
CROSS APPLY funOrderAmount(O.OrderID) OA

如果使用 OUTER APPLY ,若 right table 沒有結果,就會得到 NULL

SELECT O.OrderID, O.CustomerID, OA.Amount
FROM Orders O
CROSS APPLY funOrderAmount(O.OrderID) OA

沒有留言:

張貼留言