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

本文转自baoqiangwang51CTO博客，原文链接：http://blog.51cto.com/baoqiangwang/310459，如需转载请自行联系原作者