Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

SQL Server的子查询给人的感觉一向不是很好用，IN子查询无法实现多列的子查询，很多情况下又需要进行自我的子查询操作，比如取员工的最新订单之类的问题。

以下SQL和案例来之于技术内幕 T-SQL查询>一书，不过适当的做了些编排和自己的理解。

让我们先来看看Oracle是怎么处理子查询的

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

ITPub博客

关于SQLServer2005的学习笔记——子查询

最新文章