何时使用表运算符APPLY [英] When to use the table operator APPLY

问题描述

我试图了解表运算符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.
所以我想知道使用在sql-server 2005中实现的standard sql来获得与上述相同的结果将有多困难? APPLY是否会使查询更短或更易读?
如果此示例没有显示出使用APPLY的大量优势，那么什么是使用APPLY有优势的明显示例呢?

My main hurdle is understanding when to use APPLY.
So I'm wondering how difficult would it be to get the same result as above using standard sql implemented in sql-server 2005 ?
Does APPLY make the query a lot shorter or more readable?
If this example shows no massive advantages to using APPLY then what is a clear example where there is an advantage to using APPLY?

推荐答案

首先-使用apply，您可以调用表值函数，其中参数值取自查询表，像这样:

First of all - with apply you could call table-valued functions where parameter values are taken from table you query, something like this:

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在您需要进行一些预计算时非常有用:

From my expirience, apply is very useful when you have to make some precalculation:

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的另一个示例是 unpivot 操作:

another example of using apply is unpivot operation:

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)

最后，这是根据SQL 2005实现的您的查询，而没有使用 apply :

finally, here's your query implemented in terms of SQL 2005 without using apply:

;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

请参见 sql小提琴 示例

这篇关于何时使用表运算符APPLY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！