SQL Server-聚焦APPLY运算符
来源:互联网 发布:惠州人民政府网络问政 编辑:程序博客网 时间:2024/05/18 10:50
初探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 TSQL2012GOSELECT C.custid, A.orderid, A.orderdateFROM 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 AdventureWorks2012GOSELECT SalesOrderID ,OrderDate ,MaxUnitPrice =(SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)FROM Sales.SalesOrderHeader AS soh
如上操作看似代码比较简洁也能完成我们的查询诉求,但是我们用派生表来进行查询又是怎样的呢?
USE AdventureWorks2012GOSELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_priceFROM Sales.SalesOrderHeader AS sohJOIN( SELECT max_unit_price = MAX(sod.UnitPrice), SalesOrderID FROM Sales.SalesOrderDetail AS sod GROUP BY sod.SalesOrderID) sodON sod.SalesOrderID = soh.SalesOrderID
此时由于两个表完全不相关,我们需要通过GROUP BY完成再进行JOIN,代码不是显得非常臃肿吗,这还是简单的,当有多个表时就比较复杂了,导致代码就不再具有可读性。但是自从在SQL Server 2005中有了APPLY妈妈再也不用担心我读不懂复杂的代码了,我们看看CROSS APPLY是怎样实现的。
USE AdventureWorks2012GOSELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_priceFROM Sales.SalesOrderHeader AS sohCROSS 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 AdventureWorks2012GOSELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_price ,sod.sum_line_totalFROM Sales.SalesOrderHeader AS sohCROSS 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_priceFROM Sales.SalesOrderHeader AS sohLEFT JOIN( SELECT max_unit_price = MAX(sod.UnitPrice), SalesOrderID FROM Sales.SalesOrderDetail AS sod GROUP BY sod.SalesOrderID) sodON sod.SalesOrderID = soh.SalesOrderID
此时我们利用OUTER APPLY则是如下:
USE AdventureWorks2012GOSELECT soh.SalesOrderID ,soh.OrderDate ,sod.max_unit_priceFROM Sales.SalesOrderHeader AS sohOUTER APPLY( SELECT max_unit_price = MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail AS sod WHERE soh.SalesOrderID = sod.SalesOrderID) sod
上述对于APPLY右侧表表达式是一个派生表,此时为了封装,我们可以使用TVF内嵌表值函数来实现。其实将内嵌表值函数来代替派生表实现每个客户最近的3个订单。首先我们封装一个表值函数
USE TSQL2012GOIF OBJECT_ID('dbo.TopOrders') IS NOT NULL DROP FUNCTION dbo.TopOrders;GOCREATE FUNCTION dbo.TopOrders (@custid AS INT, @n AS INT) RETURNS TABLEAS 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 TSQL2012GOSELECT C.custid, C.companyname, A.orderid, A.empid, A.requireddateFROM Sales.Customers AS C CROSS APPLY dbo.TopOrders(C.custid, 3) AS A;
上面我们通过封装内嵌表值函数代替派生表使代码更具可读性和可维护性。到此我们可以得出一点基本结论。
APPLY运算符使用分析结论:当需要对表中的每一行进行应用时,且需要将所有结果集组合到一个结果集表中时,此时我们应该使用APPLY运算符,至于是使用CROSS APPLY还是OUTER APPLY根据场景而定,虽然APPLY右侧表可以用相关子查询或者派生表来实现,但是使得代码臃肿和可维护性差,通过封装内嵌表值函数来实现可以说是对右侧表通过相关子查询或者派生表来实现的完美替代者。
- SQL Server-聚焦APPLY运算符
- SQL Server 2005 APPLY 运算符
- SQL Server中以apply运算符简化join和子查询
- SQL SERVER中apply操作符
- SQL server Outer apply
- SQL Server cross apply 和outer apply
- SQL Server-聚焦UNIOL ALL/UNION查询
- APPLY运算符
- SQL Server 2005 T-SQL cross Apply 与outer apply
- SQL Server-聚焦深入理解动态SQL查询(三十二)
- SQL Server CROSS APPLY和OUTER APPLY的应用详解
- sql server中的outer apply ,across apply 对应 oracle
- SQL Server 关于CROSS APPLY 和 OUTER APPLY应用
- APPLY 运算符的使用
- Sql Server2005 Transact-SQL 新兵器学习总结之-APPLY 运算符
- 聚焦索引与非聚焦索引及其查询效率 (MS SQL SERVER)
- 介绍SQL Server 2005的CROSS Apply
- 介绍SQL Server 2005的CROSS Apply
- 使用Redux和ngrx构建更好的Angular2应用(一)
- python 调用opencv读取视频
- 关于 mBaiduMap.animateMapStatus(u);空指针异常
- Learning D3.js d3的path讲解
- java定时执行功能
- SQL Server-聚焦APPLY运算符
- 二进制文件与文本文件有哪些差异
- SpringMVC解决跨域
- 离散题目14
- 下拉框
- C#之实现Scoket心跳机制
- java动态代理
- 图片显示隐藏js
- SQL Server-表表达式基础