首先-
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
