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

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

以下SQL和案例来之于<SQLServer2005技术内幕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