|
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',TO_DATE('2000-01-21','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110003',2,'YYYY-MM-DD'));
INSERT INTO Orders VALUES('110004',3,TO_DATE('2000-02-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110005','CZH','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110006',TO_DATE('2000-03-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110007','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110008','KIDD','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110009',TO_DATE('2000-04-01','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110010','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110011','YYYY-MM-DD'));
INSERT INTO Orders VALUES('110012',TO_DATE('2000-01-10','YYYY-MM-DD'));
COMMIT;
SELECT EmployeeID,OrderDate,OrderID,CustomerID
? FROM Orders
? ORDER BY EmployeeID,OrderID
?
-- 层递直至实现唯一为止
SELECT EmployeeID,CustomerID
? FROM Orders
? WHERE (EmployeeID,OrderID) IN
?????? (SELECT EmployeeID,MAX(OrderID)
????????? FROM Orders
???????? WHERE (EmployeeID,OrderDate) IN
?????????????? (SELECT EmployeeID,Max(OrderDate)
????????????????? FROM Orders
???????????????? GROUP BY EmployeeID)
???????? GROUP BY EmployeeID,OrderDate)
? ORDER BY EmployeeID,CustomerID
?
-- 多值，通常情况下该语句即可，不过在本例中 EmployeeID,OrderDate 并不唯一，导致结果非所需的
SELECT EmployeeID,OrderDate) IN
?????? (SELECT EmployeeID,Max(OrderDate)
????????? FROM Orders
???????? GROUP BY EmployeeID)
? ORDER BY EmployeeID,CustomerID
?
--Error ，这是个错误的表达式
SELECT EmployeeID,MAX(OrderDate),MAX(OrderID)
????????? FROM Orders
???????? GROUP BY EmployeeID)
?
-- 使用分析函数，也可以实现相应的子查询 ??????
SELECT EmployeeID,CustomerID FROM
(
? SELECT EmployeeID,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),
? OrderDate?? DATETIME
);
INSERT INTO Orders VALUES('110001','2000-01-11');
INSERT INTO Orders VALUES('110002','2000-01-21');
INSERT INTO Orders VALUES('110003','2000-01-11');
INSERT INTO Orders VALUES('110004','2000-02-01');
INSERT INTO Orders VALUES('110005','2000-02-01');
INSERT INTO Orders VALUES('110006','2000-03-01');
INSERT INTO Orders VALUES('110007','2000-03-01');
INSERT INTO Orders VALUES('110008','2000-02-01');
INSERT INTO Orders VALUES('110009','2000-04-01');
INSERT INTO Orders VALUES('110010','2000-03-01');
INSERT INTO Orders VALUES('110011','2000-02-01');
INSERT INTO Orders VALUES('110012','2000-01-10');
COMMIT;
?
-- 多值，通常情况下该语句即可，不过在本例中 EmployeeID,CustomerID
? FROM Orders O1
? WHERE rderDate=
?????? (SELECT MAX(OrderDate)
????????? FROM Orders O2
???????? WHERE O1.EmployeeID=O2.EmployeeID)
? ORDER BY EmployeeID,CustomerID
等效于 Oracle 的以下语句
SELECT EmployeeID,CustomerID
?
正确的 SQLServer 子查询写法，用两个 MAX 求得唯一值
SELECT EmployeeID,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,CustomerID
?
-- 本例使用一种变通的方法，把几个应有的附加条件加进来然后返回，比较难以理解
SELECT
? CAST(SUBSTRING(BinStr,8) AS DATETIME) AS OrderDate,
? CAST(SUBSTRING(BinStr,9,6) AS VARCHAR) AS OrderID,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,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,CustomerID
?
-- 本例使用了 IN 子查询，可以自定义返回的 TOP N 条数
SELECT EmployeeID,CustomerID
? FROM Orders O1
? WHERE OrderID IN
? (SELECT TOP(1) OrderID
???? FROM Orders O2
??? WHERE O1.EmployeeID=O2.EmployeeID
??? ORDER BY OrderDate DESC,CustomerID
?? )
ORDER BY EmployeeID,CustomerID
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
