主页 / dba / 问题 / 86143
Accepted
Shaul Behr
Asked: 2014-12-15 06:36:40 +0800 CST

如何使用表值函数连接表？

  • 772

我有一个用户定义的功能：

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

现在我想在另一张桌子上加入这个，像这样：

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f 
join ut_FooFunc(f.ID, 1) u -- doesn't work
where f.SomeCriterion = 1

换句话说，对于所有为 1 的Foo记录SomeCriterion，我想查看Foo ID和Desc，以及为 的输入返回的Field1和的值。Field2ut_FooFuncFoo.ID

这样做的语法是什么？

sql-server sql-server-2012

2 个回答

  1. Best Answer

    你不需要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 = ...
    • 116

  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.

    我在这里看到的是内部查询带来了更正确的结果集，并且执行时间更加高效。请用更好的方法纠正我以完成同样的事情！

    • 1

相关问题