我有一个用户定义的函数:

create function ut_FooFunc(@fooID bigint, @anotherParam tinyint)
returns @tbl Table (Field1 int, Field2 varchar(100))
as
begin
  -- blah blah
end


现在我想将此对象加入另一个表,例如:
换句话说,对于Foo为1的所有SomeCriterion记录,我想查看FooIDDesc,以及从Field1返回的Field2ut_FooFunc的值,以输入Foo.ID

执行此操作的语法是什么?

#1 楼

您需要CROSS APPLY不联接。

联接中涉及的表表达式的定义必须稳定。即不能将它们关联起来,以使表表达式的含义有所不同,具体取决于另一个表中一行的值。

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f 
Cross apply ut_FooFunc(f.ID, 1) u
where f.SomeCriterion = ...


#2 楼

我知道线程很旧,有人问我同样的问题,我做了一个测试,结果如下...


FacCurrencyRate = 14264中的记录,而TestFunction如果独立执行则返回105 。


    SELECT F.*, x.CurrencyKey, x.CurrencyName
    FROM ( 
           SELECT CurrencyKey, CurrencyName FROM dbo.TestFunction()
        ) x
    INNER JOIN [dbo].[FactCurrencyRate] F ON x.CurrencyKey = f.CurrencyKey;


执行时间是...

    (14264 rows affected)
    Table 'FactCurrencyRate'. Scan count 1, logical reads 75, physical reads 1, read-ahead reads 73, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCurrency'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 749 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


如果我使用建议的答案如下...

select F.*, x.CurrencyKey, x.CurrencyName from [dbo].[FactCurrencyRate] F
cross apply dbo.TestFunction() x


执行时间和结果计数为...

(1497720 rows affected)
Table 'FactCurrencyRate'. Scan count 1, logical reads 75, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 38110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCurrency'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2106 ms,  elapsed time = 43242 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


我在这里看到的是内部查询带来了更正确的结果集,执行时间更加高效。用更好的方法纠正我,以达到同样的效果!

评论


交叉应用应用于每个外部行(因此逐行),这就是为什么它如此慢得多的原因,尤其是在较大的数据集上。通过反转它(因为您只想要在TVF中具有匹配项的结果,则可以大大减少执行时间(没有调用什么也不做)以及返回的行数。但是,您的两个语句与第二个语句不相等返回的行比第一行多得多。就正确性而言,这取决于您的业务需求。

–乔纳森·菲特(Jonathan Fite)
19年4月18日在12:17

是的我同意。但是,我是根据最初的问题编写代码的,当时我假设表之间存在一对多的关系。其次,在面试中有人问我一个问题。谢谢

–user3104116
19年4月19日在13:10