關於SQLServer2005的學習筆記——子查詢

--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

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