何时使用表运算符APPLY

一尘不染

何时使用表运算符APPLY

sql

我试图了解表运算符APPLY。

这是示例：

CREATE TABLE #y ( Name char(8), hoursWorked int);
GO
INSERT INTO #y VALUES ('jim',4);
INSERT INTO #y VALUES ('michael',40);
INSERT INTO #y VALUES ('raj',1000);
INSERT INTO #y VALUES ('jason',7);
INSERT INTO #y VALUES ('tim',50);
GO

CREATE TABLE #x ( Name char(8),game char(8), NumBets int);
GO
INSERT INTO #x VALUES ('jim','chess',4);
INSERT INTO #x VALUES ('jim','BG',10);
INSERT INTO #x VALUES ('jim','draughts',100);
INSERT INTO #x VALUES ('jim','football',5);
INSERT INTO #x VALUES ('michael','chess',40);
INSERT INTO #x VALUES ('michael','BG',7);
INSERT INTO #x VALUES ('michael','draughts',65);
INSERT INTO #x VALUES ('michael','football',50);
INSERT INTO #x VALUES ('raj','chess',400);
INSERT INTO #x VALUES ('raj','BG',70);
INSERT INTO #x VALUES ('raj','draughts',650);
INSERT INTO #x VALUES ('tim','draughts',60000);
GO

SELECT  y.Name, 
        y.hoursWorked,
        x.game,
        x.NumBets
FROM    #y y
        OUTER APPLY 
          (
          SELECT TOP 2 *
          FROM   #x
          WHERE  Name = y.Name 
          ORDER BY NumBets
        ) x
ORDER BY  y.Name,
        x.NumBets DESC;

我的主要障碍是了解何时使用APPLY。
所以我想知道使用standard sqlin实现的结果与上述相同将有多困难sql-server 2005？
是否APPLY会使查询更短或更易读？
如果该示例没有显示使用的巨大优势，APPLY那么什么是使用显着的优势APPLY呢？

阅读 194

2021-03-08

共1个答案

一尘不染

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

select
    t1.col3, -- column from table
    f1.col1  -- column from function
from 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 value
from 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 used
from 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.value
from 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, NumBets
from cte
where row_num <= 2
order by Name, NumBets desc

2021-03-08