何时使用表运算符APPLY

何时使用表运算符APPLY

首先-

apply

可以调用 表值函数 ，其中参数值是从查询的表中获取的，如下所示：

select    t1.col3, -- column from table    f1.col1  -- column from functionfrom table1 as t1    left outer join table2 as t2 on t2.col1 = t1.col1    outer apply dbo.function1(t1.col1, t2.col2) as f1

或 切碎xml列

select    t1.col3,    t.c.value('@value', 'int') as valuefrom table1 as t1    -- table1.col1 is xml iike <Data @Value="...">...</Data>    outer apply t1.col1.nodes('Data') as t(c)

根据我的经验，

apply

当您需要进行一些 预先计算 时，它非常有用：

select    t1.col3,    a1.col1,  --calculated value    a2.col1   -- another calculated value, first one was usedfrom table1 as t1    outer apply (select t1.col1 * 5 as col1) as a1    outer apply (select a1.col1 - 4 as col1) as a2

使用的另一个示例

apply

是 不可透视的 *** 作：

select    t1.col1, c.name, c.valuefrom table1 as t1    outer apply (        select 'col1', t1.col1 union all        select 'col2', t1.col2    ) as c(name, value)

最后，这是在不使用 apply的* 情况下根据SQL 2005实现的 查询 ： *

;with cte as (    select        y.Name,         y.hoursWorked,        x.game,        x.NumBets,        row_number() over(partition by x.Name order by x.NumBets) as row_num    from y        left outer join x on x.Name = y.Name)select Name, hoursWorked, game, NumBetsfrom ctewhere row_num <= 2order by Name, NumBets desc