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

bq_wang發表於2010-01-11
SQLServer筆記
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部落格 ” ，連結：http://blog.itpub.net/6517/viewspace-624818/，如需轉載，請註明出處，否則將追究法律責任。

相關文章