我试图了解表运算符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。 所以我想知道使用standard sqlin实现的结果与上述相同将有多困难sql-server 2005? 是否APPLY会使查询更短或更易读? 如果该示例没有显示使用的巨大优势,APPLY那么什么是使用显着的优势APPLY呢?
standard sql
sql-server 2005
首先-apply可以调用 表值函数 ,其中参数值是从查询的表中获取的,如下所示:
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