表运算符: Apply运算符, 递归CTE(公共表表达式)

Apply运算符支持两种形式

cross apply和outer apply

apply运算符对两个输入表进行操作, 其中第二个可以是表表达式, 我们将他们分别称为左表和右表.

右表通常是一个表表达式或者内联表值函数.

                

cross apply运算符实现了一个逻辑查询处理步骤: 把右表表达式应用到左表的每一行, 再把结果集组合起来, 生成一个统一的结果表.

select * from Sales.Shippers s cross join HR.Employees e
select * from Sales.Shippers s cross apply HR.Employees e

这两条语句生成的结果是一样的. 和连接不同的是, 使用cross apply操作符的时候, 对于左表中的每一行, 右表表达式可以代表不同的数据行集合. 理解上面加粗的那句话就是, 在右表中的查询可以引用左表的列: 如果右表是个派生表, 可以引用左表列; 如果右表是个内联表值函数, 可以将左表的列作为参数进行传递

/*返回每个客户最新的三个订单*/
select c.custid,t.orderid,t.orderdate from Sales.Customers c cross apply
    (select top 3 * from Sales.Orders o where o.custid= c.custid order by o.orderdate desc) t

如果使用outer apply, 那么将会返回左表列在右表表达式为空集的数据.

递归CTE

declare @dt table(id int, pid int)
insert into @dt
select 1,0 union
select 2,0 union
select 3,1 union
select 4,1 union
select 5,3;

with t as(
    select id,pid from @dt where id=1/*第一次结果集*/
    union all
    select d.id,d.pid from @dt d inner join t
    on d.pid=t.id/*用第二个结果集的父ID去匹配第一个结果集的ID, 直到出现空集合位置*/
/*查询部门为1下的所有子部门.*/
)
select * from t

另一个例子

with t as(
select e.empid,e.mgrid,e.firstname,e.lastname from HR.Employees e where empid=9
union all
select e2.empid,e2.mgrid,e2.firstname,e2.lastname from HR.Employees e2
inner join t on e2.empid=t.mgrid
)
select * from t
/*查询empid为9的所有上级*/

具体见sql server 2008技术内幕 t-sql语言基础 171页