`

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

分享到：
| VB.Net 中的定时器类Timer
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    学习笔记——sql.zip

    "学习笔记——sql.zip"这个压缩包文件很可能包含了关于SQL的学习资料，如教程、笔记、示例代码等，旨在帮助用户掌握SQL的基本概念、语法和高级特性。 首先，SQL的基础知识包括数据类型，如整型（INT）、浮点型...

    SQL常用的增删改查语句、视屏笔记[定义].pdf

    SQL，全称Structured Query Language，即结构化查询语言，是一种国际标准的数据管理和处理语言，...在实际工作中，还需要学习更复杂的SQL技术，如联接、子查询、聚合函数和窗口函数等，以应对各种复杂的数据处理场景。

    【学习笔记】温习sql及MySQL的使用（数据库第2次实验）

    在本篇【学习笔记】中，我们主要复习了SQL语言及其在MySQL中的应用，特别是针对数据库的第二次实验，重点在于掌握交互式SQL操作。实验旨在让学习者熟悉数据库的SQL工具，通过实际操作来加深对SQL语言的理解，并完成...

    学习mysql以及sequelize时的笔记 .zip

    6. 查询构建器：Sequelize提供了一个强大的查询构造器，允许动态构建复杂的SQL查询，包括联接、子查询和聚合函数。 7. 迁移：Sequelize的迁移系统允许开发者管理数据库结构的变化，方便在不同环境中同步数据库。 8...

    软考 数据库工程师 电子教程

    “数据库工程师”这一职位需要对数据库管理系统的运作有深入了解，包括关系型数据库理论，如ER模型、范式理论等，以及SQL的使用，如数据查询、更新、插入和删除操作。此外，他们还需要熟悉各种数据库产品，如Oracle...

    达内笔记（达内助教整理的）

    PL/SQL 是 Oracle 数据库提供的过程化语言，它是 SQL 的扩展，增加了流程控制、异常处理和子程序等特性。PL/SQL 代码块可以在数据库服务器上执行，提高了数据库操作的效率。它支持函数、存储过程、触发器和游标等...

    Oracle大总结（达内培训笔记）

    ### Oracle大总结...以上内容总结了达内培训期间关于Oracle的学习笔记，详细介绍了数据库的基本概念、SQL语言的基础知识以及相关的高级特性。希望这些知识点能够帮助读者更好地理解和掌握Oracle数据库的相关技术。

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，它产生于距今五十年前。简单来说是本身可视...

    C#学习记录-图书馆管理系统(源码)

    在C#中，可以使用ADO.NET库来操作SQL Server或其他数据库，例如创建数据连接、执行SQL语句、填充数据集等。 3. **用户界面设计**：UI设计是系统的重要组成部分，用户可以通过它来执行各种操作。C#中的Windows Forms...

    计算机英语

    4. SQL（Structured Query Language）：结构化查询语言，用于数据库管理和查询的语言。 5. TCP（Transmission Control Protocol）：传输控制协议，互联网中常用的数据传输协议。 6. OS（Operating System）：操作...

    ireport教程【经典2】

    【压缩包内容】：jasperreport＋ireport学习笔记1.pdf是压缩包内的主要资料，这份PDF文档可能是对iReport使用技巧的详细讲解，涵盖了从安装配置到实际操作的全过程。 以下是iReport的一些关键知识点： 1. **安装与...

Magicbox
Global site tag (gtag.js) - Google Analytics