关于SQLServer2005的学习笔记——子查询

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:
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 ,如需转载请自行联系原作者
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
6月前
|
SQL 数据库
SQL Server 连接查询和子查询
SQL Server 连接查询和子查询
106 0
|
SQL 测试技术 Go
SQL Server中INNER JOIN与子查询IN的性能测试
这个月碰到几个人问我关于“SQL SERVER中INNER JOIN 与 IN两种写法的性能孰优孰劣?”这个问题。其实这个概括起来就是SQL Server中INNER JOIN与子查询孰优孰劣(IN是子查询的实现方式之一,本篇还是只对比INNER JOIN与子查询IN的性能,如果展开INNER JOIN与子查询性能对比,范围太大了,没法一一详述)。
2205 0
|
SQL 测试技术 Go
SQL Server中INNER JOIN与子查询IN的性能测试
原文:SQL Server中INNER JOIN与子查询IN的性能测试 这个月碰到几个人问我关于“SQL SERVER中INNER JOIN 与 IN两种写法的性能孰优孰劣?”这个问题。其实这个概括起来就是SQL Server中INNER JOIN与子查询孰优孰劣(IN是子查询的实现方式之一,本篇还是只对比INNER JOIN与子查询IN的性能,如果展开INNER JOIN与子查询性能对比,范围太大了,没法一一详述)。
1292 0
|
SQL 数据库 C++
在SQL Server中为什么不建议使用Not In子查询
原文:在SQL Server中为什么不建议使用Not In子查询     在SQL Server中,子查询可以分为相关子查询和无关子查询,对于无关子查询来说,Not In子句比较常见,但Not In潜在会带来下面两种问题: 结果不准确 查询性能低下       下面我们来看一下为什么尽量不使用Not In子句。
979 0
|
SQL 存储 数据库
SQL Server调优系列基础篇(子查询运算总结)
原文:SQL Server调优系列基础篇(子查询运算总结) 前言 前面我们的几篇文章介绍了一系列关于运算符的介绍,以及各个运算符的优化方式和技巧。其中涵盖:查看执行计划的方式、几种数据集常用的连接方式、联合运算符方式、并行运算符等一系列的我们常见的运算符。
1149 0

相关课程

更多