如何使用表值函式連接表？

我有一個使用者定義的功能：

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和的值。Field2``ut_FooFunc``Foo.ID

這樣做的語法是什麼？

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

我知道執行緒很舊，我被問到同樣的問題，我做了一個測試，結果如下……

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.

我在這裡看到的是內部查詢帶來了更正確的結果集，並且執行時間更加高效。請用更好的方法糾正我以完成同樣的事情！

引用自：https://dba.stackexchange.com/questions/86143