关于SQLServer2005的学习笔记――子查询
发布时间：2020-12-12 15:27:09 所属栏目：MsSql教程 来源：网络整理
导读：SQL Server 的子查询给人的感觉一向不是很好用， IN 子查询无法实现多列的子查询，很多情况下又需要进行自我的子查询操作，比如取员工的最新订单之类的问题。以下 SQL 和案例来之于 SQLServer2005 技术内幕 T-SQL 查询 一书，不过适当的做了些编排和自己的理
|
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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |