SQL Server-聚焦APPLY运算符

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介:

初探APPLY运算符

APPLY运算符是一个非常强大的表运算符,但是APPLY不是标准的,相对应的标准叫做LATERAL,但是此标准并未在SQL Server中实现。像所有表运算符一样,该运算符用于查询的FROM子句中。APPLY运算符支持的类型是CROSS APPLY和OUTER APPLY。CROSS APPY仅仅实施一个逻辑查询处理阶段,而OUTER APPLY实施了两个阶段,APPLY运算符对两个输入表进行操作,第二个可以是一个表表达式,我们将APPLY两侧的表分别叫做左侧表和右侧表,右侧表通常是一个派生表或TVF(内嵌表值函数)。CROSS APPLY运算符实施一个逻辑查询处理阶段-它将右侧的表表达式应用到左侧表的每一行,并生成一个组合结果集的结果表。CROSS APPLYl类似于交叉联接中的CROSS JOIN,但是使用CROSS APPLY运算符,右侧的表表达式可以对来自左侧表的每一行表示一个不同的行集,这是与联接的不同之处。当在右侧使用一个派生表,并且派生表查询中引用来自左侧表的属性,就可以实现此目标,或者是在右侧使用一个内嵌TVF,可以传递左侧的属性作为输入参数,同样可以实现此目的-摘抄自SQL Server 2012基础教程。下面我们看一个简单的例子。

USE TSQL2012
GO

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
    CROSS APPLY
        (SELECT TOP(3) orderid, empid, orderdate, requireddate 
        FROM Sales.Orders AS O
        WHERE O.custid = C.custid
        ORDER BY orderdate DESC, orderid DESC) AS A;

上述完成的是返回每个客户最近的3个订单。我们可以将右侧的表表达式看做是一个相关子查询,右侧的表表达式通过引用custid对来自Customers表的每一行进行处理并返回每个客户的最近的3个订单,是不是看起来很清爽呢,下面我们将进一步探讨APPLY运算符的作用。

进一步探讨APPLY运算符

上面我们看到通过相关子查询来进行查询显得代码有点丑陋,我们再来看一个例子。查询每个单价最高的订单,我们通过子查询来实现。

CROSS APPLY

USE AdventureWorks2012
GO

SELECT 
     SalesOrderID
    ,OrderDate
    ,MaxUnitPrice =(SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM Sales.SalesOrderHeader AS soh

如上操作看似代码比较简洁也能完成我们的查询诉求,但是我们用派生表来进行查询又是怎样的呢? 

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
JOIN
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice),
        SalesOrderID
    FROM Sales.SalesOrderDetail AS sod
    GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID

此时由于两个表完全不相关,我们需要通过GROUP BY完成再进行JOIN,代码不是显得非常臃肿吗,这还是简单的,当有多个表时就比较复杂了,导致代码就不再具有可读性。但是自从在SQL Server 2005中有了APPLY妈妈再也不用担心我读不懂复杂的代码了,我们看看CROSS APPLY是怎样实现的。

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
CROSS APPLY
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice)
    FROM Sales.SalesOrderDetail AS sod
    WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

当我们利用内部联接时此时JOIN中的查询是独立的所以需要进行GROUP BY,而对于CROSS APPLY它本身就是对来自左侧的表中每一行就行处理并返回,同时利用CROSS APPLY它也超越了相关子查询,比如说我们还需要查出每个订单的总价呢,我们利用相关子查询需要再次嵌入SELECT子句。

SELECT 
     SalesOrderID           
    ,OrderDate              
    ,MaxUnitPrice           = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
    ,SumLineTotal           = (SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM Sales.SalesOrderHeader AS soh

而利用CROSS APPLY只需添加集合函数SUM即可

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
    ,sod.sum_line_total
FROM Sales.SalesOrderHeader AS soh
CROSS APPLY
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice)
        ,sum_line_total = SUM(sod.LineTotal)
    FROM Sales.SalesOrderDetail AS sod
    WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

OUTER APPLY

对于OUTER APPLY,如果右侧的表表达式返回一个空集合,CROSS APPLY运算符不会返回相应的左侧行,也就是说OUTER APPLY和在派生表上进行LEFT JOIN是等同的,如下:

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
LEFT JOIN
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice),
        SalesOrderID
    FROM Sales.SalesOrderDetail AS sod
    GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID

此时我们利用OUTER APPLY则是如下:

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
OUTER APPLY
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice)
    FROM Sales.SalesOrderDetail AS sod
    WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

上述对于APPLY右侧表表达式是一个派生表,此时为了封装,我们可以使用TVF内嵌表值函数来实现。其实将内嵌表值函数来代替派生表实现每个客户最近的3个订单。首先我们封装一个表值函数

USE TSQL2012
GO

IF OBJECT_ID('dbo.TopOrders') IS NOT NULL
    DROP FUNCTION dbo.TopOrders;
GO

CREATE FUNCTION dbo.TopOrders
    (@custid  AS INT, @n  AS  INT)
    RETURNS TABLE
AS RETURN

    SELECT  orderid, empid, orderdate, requireddate
    FROM Sales.Orders
    WHERE  custid = @custid
    ORDER BY orderdate DESC, orderid DESC
    OFFSET 0 ROWS FETCH FIRST @n ROWS ONLY;
GO

接着利用CROSS APPLY进行查询。

USE TSQL2012
GO

SELECT C.custid, C.companyname, A.orderid, A.empid, A.requireddate
FROM Sales.Customers AS C
 CROSS APPLY dbo.TopOrders(C.custid, 3) AS A;

上面我们通过封装内嵌表值函数代替派生表使代码更具可读性和可维护性。到此我们可以得出一点基本结论。

APPLY运算符使用分析结论:当需要对表中的每一行进行应用时,且需要将所有结果集组合到一个结果集表中时,此时我们应该使用APPLY运算符,至于是使用CROSS APPLY还是OUTER APPLY根据场景而定,虽然APPLY右侧表可以用相关子查询或者派生表来实现,但是使得代码臃肿和可维护性差,通过封装内嵌表值函数来实现可以说是对右侧表通过相关子查询或者派生表来实现的完美替代者。




本文转自 sshpp 51CTO博客,原文链接:http://blog.51cto.com/12902932/1926496,如需转载请自行联系原作者
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2天前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
|
6天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
27 3
|
10天前
|
SQL 数据库
SQL AND运算符
【7月更文挑战第11天】SQL AND运算符。
9 2
|
12天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
23小时前
|
SQL 存储 数据库
深入理解 SQL UNION 运算符及其应用场景
【7月更文挑战第8天】SQL UNION 概述 `UNION` 运算符结合多个`SELECT`语句,生成不含重复行的结果集。基本语法是:`SELECT...FROM table1 UNION SELECT...FROM table2`。适用于整合相同结构数据表、不同条件查询结果及跨数据库数据。注意列数和数据类型需匹配,排序规则一致,大量操作可能影响性能。示例:合并`Students_Math`和`Students_Science`表中`StudentID`和`Grade`的数据。
|
23小时前
|
SQL 监控 数据库
SQL Server 查询超时问题排查
【7月更文挑战第8天】排查 SQL Server 查询超时涉及五个主要方面:检查复杂查询、评估服务器性能、审视配置参数、更新统计信息和分析执行计划。关注点包括查询的结构(如连接、子查询和索引),服务器资源(CPU、内存、网络延迟),连接和内存设置,以及统计信息的时效性。通过这些步骤可定位并解决性能瓶颈。
|
3天前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
10 0
|
15天前
|
SQL 存储 数据库
SQL Server性能优化策略与实践
在使用Microsoft SQL Server(简称MSSQL)进行数据库管理时,性能优化是确保系统高效运行、提升用户体验的关键环节
|
1月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
254 1