關於SQLServer2005的學習筆記——子查詢
SQL Server 的子查詢給人的感覺一向不是很好用， IN 子查詢無法實現多列的子查詢，很多情況下又需要進行自我的子查詢操作，比如取員工的最新訂單之類的問題。 以下 SQL 和案例來之於
CREATE TABLE Orders
( OrderID VARCHAR2(6),
CustomerID VARCHAR2(6),
EmployeeID INT, OrderDate DATE );
TRUNCATE TABLE Orders;
INSERT INTO Orders VALUES('110001','WBQ',1,TO_DATE('2000-01-11','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110002','WBQ',1,TO_DATE('2000-01-21','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110003','WBQ',2,TO_DATE('2000-01-11','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110004','WBQ',3,TO_DATE('2000-02-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110005','CZH',1,TO_DATE('2000-02-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110006','CZH',2,TO_DATE('2000-03-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110007','CZH',2,TO_DATE('2000-03-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110008','KIDD',3,TO_DATE('2000-02-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110009','KIDD',1,TO_DATE('2000-04-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110010','KIDD',2,TO_DATE('2000-03-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110011','KIDD',3,TO_DATE('2000-02-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110012','CZH',3,TO_DATE('2000-01-10','YYYY-MM-DD'));
COMMIT;
SELECT EmployeeID,OrderDate,OrderID,CustomerID FROM Orders ORDER BY EmployeeID,OrderDate,OrderID
-- 層遞直至實現唯一為止
SELECT EmployeeID,OrderDate,OrderID,CustomerID FROM Orders
WHERE (EmployeeID,OrderDate,OrderID) IN (SELECT EmployeeID,OrderDate,MAX(OrderID) FROM Orders WHERE (EmployeeID,OrderDate) IN (SELECT EmployeeID,Max(OrderDate) FROM Orders GROUP BY EmployeeID) GROUP BY EmployeeID,OrderDate) ORDER BY EmployeeID,OrderDate,OrderID,CustomerID -- 多值，通常情況下該語句即可，不過在本例中 EmployeeID,OrderDate 並不唯一，導致結果非所需的
SELECT EmployeeID,OrderDate,OrderID,CustomerID
FROM Orders
WHERE (EmployeeID,OrderDate) IN (SELECT EmployeeID,Max(OrderDate) FROM Orders GROUP BY EmployeeID)
ORDER BY EmployeeID,OrderDate,OrderID,CustomerID
--Error ，這是個錯誤的表達式
SELECT EmployeeID,OrderDate,OrderID,CustomerID FROM Orders
WHERE (EmployeeID,OrderDate,OrderID) IN (SELECT EmployeeID,MAX(OrderDate),MAX(OrderID)
FROM Orders GROUP BY EmployeeID)
-- 使用分析函數，也可以實現相應的子查詢
SELECT EmployeeID,OrderDate,OrderID,CustomerID
FROM ( SELECT EmployeeID,OrderDate,OrderID,CustomerID, RANK() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC,OrderID DESC) Rank FROM Orders ) a WHERE a.Rank=1
讓我們繼續看看 SQL Server 是如何處理的
CREATE TABLE Orders
( OrderID VARCHAR(6),
CustomerID VARCHAR(6),
EmployeeID INT,
OrderDate DATETIME );
INSERT INTO Orders VALUES('110001','WBQ',1,'2000-01-11');
INSERT INTO Orders VALUES('110002','WBQ',1,'2000-01-21');
INSERT INTO Orders VALUES('110003','WBQ',2,'2000-01-11');
INSERT INTO Orders VALUES('110004','WBQ',3,'2000-02-01');
INSERT INTO Orders VALUES('110005','CZH',1,'2000-02-01');
INSERT INTO Orders VALUES('110006','CZH',2,'2000-03-01');
INSERT INTO Orders VALUES('110007','CZH',2,'2000-03-01');
INSERT INTO Orders VALUES('110008','KIDD',3,'2000-02-01');
INSERT INTO Orders VALUES('110009','KIDD',1,'2000-04-01');
INSERT INTO Orders VALUES('110010','KIDD',2,'2000-03-01');
INSERT INTO Orders VALUES('110011','KIDD',3,'2000-02-01');
INSERT INTO Orders VALUES('110012','CZH',3,'2000-01-10');
COMMIT;
-- 多值，通常情況下該語句即可，不過在本例中 EmployeeID,OrderDate 並不唯一，導致結果非所需的
SELECT EmployeeID,OrderDate,OrderID,CustomerID FROM Orders O1
WHERE rderDate= (SELECT MAX(OrderDate) FROM Orders O2 WHERE O1.EmployeeID=O2.EmployeeID) ORDER BY EmployeeID,OrderDate,OrderID,CustomerID 等效於 Oracle 的以下語句
SELECT EmployeeID,OrderDate,OrderID,CustomerID FROM Orders
WHERE (EmployeeID,OrderDate) IN (SELECT EmployeeID,Max(OrderDate)
FROM Orders GROUP BY EmployeeID) ORDER BY EmployeeID,OrderDate,OrderID,CustomerID
正確的 SQLServer 子查詢寫法，用兩個 MAX 求得唯一值
SELECT EmployeeID,OrderDate,OrderID,CustomerID FROM Orders O1 WHERE rderDate= (SELECT MAX(OrderDate) FROM Orders O2 WHERE O1.EmployeeID=O2.EmployeeID) AND rderID= (SELECT Max(OrderID) FROM Orders O2 WHERE O1.EmployeeID=O2.EmployeeID AND O1.OrderDate=O2.OrderDate) ORDER BY EmployeeID,OrderDate,OrderID,CustomerID
-- 本例使用一種變通的方法，把幾個應有的附加條件加進來然後返回，比較難以理解
SELECT CAST(SUBSTRING(BinStr,1,8) AS DATETIME) AS OrderDate, CAST(SUBSTRING(BinStr,9,6) AS VARCHAR) AS OrderID, CAST(SUBSTRING(BinStr,15,6) AS VARCHAR) AS CustomerID FROM (SELECT EmployeeID, MAX(CAST(OrderDate AS BINARY(8)) +CAST(OrderID AS BINARY(6)) +CAST(CustomerID AS BINARY(6))) AS BinStr FROM Orders GROUP BY EmployeeID) D;
-- 本例中在子查詢中使用 TOP+Order 排序的方式獲取相應的第一行值 SELECT EmployeeID,OrderDate,OrderID,CustomerID FROM Orders O1 WHERE rderID= (SELECT TOP(1) OrderID FROM Orders O2 WHERE O1.EmployeeID=O2.EmployeeID ORDER BY OrderDate DESC,OrderID DESC,CustomerID ) ORDER BY EmployeeID,OrderDate,OrderID,CustomerID
-- 本例使用了 IN 子查詢，可以自定義返回的 TOP N 條數
SELECT EmployeeID,OrderDate,OrderID,CustomerID FROM Orders O1 WHERE OrderID IN (SELECT TOP(1) OrderID FROM Orders O2 WHERE O1.EmployeeID=O2.EmployeeID ORDER BY OrderDate DESC,OrderID DESC,CustomerID ) ORDER BY EmployeeID,OrderDate,OrderID,CustomerID
