一,背景:网上找关于查看SQL执行计划的SQL时,发现这样一条SQL:
SELECT cp.usecounts as '使用次数' ,objtype as '类型' ,st.text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE st.text not like '%sys%'
这句SQL(’ CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st')神奇了,竟然能够连接带参数的函数(参数不是固定的值,是另一张表的某个字段)。而正常我们如果像这样’ CROSS JOIN sys.dm_exec_sql_text(plan_handle) AS st’连接时,会得到下面的错误’ The multi-part identifier " plan_handle " could not be bound.’.。
二,OSS JOIN详解
Apply操作符的功能:
可以让select查询语法与表值函数(TVF)进一步结合,在数据表含有XML字段时,结合XML的内容转换为新的数据表字段颇为方便.
Apply 工作原理:
Apply操作符让符合查询的每一条记录都调用一次TVF函数,并将结果与原数据表的记录内容一起展开.
Apply操作符定义在From子句内,使用方式与Join操作符类似. 其格式有两种类型:
CROSS APPLY
OUTER APPLY
两者的差异只在当外部的数据表逐条将记录带入到TVF之后,若TVF并无符合的记录返回时, CROSS APPLY运算将不会显示该条记录,而OUTER APPLY依然会显示外部数据表对应的记录,但TVF相关的字段则以NULL显示.
三,示例
初始化数据(下面仅仅是展示下CROSS APPLY的应用,例子举的差):
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dept]')) DROP TABLE [dbo].[Dept] GO CREATE TABLE dbo.Dept( id INT, name VARCHAR(50) ) GO INSERT INTO dbo.Dept SELECT 1,'软件工程学院' UNION ALL SELECT 2,'美术学院' UNION ALL SELECT 3,'外语学院' SELECT * FROM dbo.Dept IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]')) DROP TABLE [dbo].[Employee] GO CREATE TABLE dbo.Employee( id INT, name VARCHAR(50), dept_id INT ) GO INSERT INTO dbo.Employee SELECT 1,'Mike',1 UNION ALL SELECT 2,'Ross',1 UNION ALL SELECT 3,'Jion',2 SELECT * FROM dbo.Employee
创建多函数,并使用CROSS JOIN 与OUTER JOIN:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEployeeCountByDeptID]')) DROP FUNCTION [dbo].[GetEployeeCountByDeptID] GO CREATE FUNCTION dbo.GetEployeeCountByDeptID ( @dept_id INT ) RETURNS TABLE AS RETURN ( SELECT E.dept_id, COUNT(*) AS employee_num FROM Employee AS E WHERE E.dept_id=@dept_id GROUP BY E.dept_id ) GO SELECT * FROM dbo.Dept AS D CROSS APPLY dbo.GetEployeeCountByDeptID(D.id) SELECT * FROM dbo.Dept AS D OUTER APPLY dbo.GetEployeeCountByDeptID(D.id)