正确使用表值函数 [英] Proper use of a table-valued function

问题描述

我有一个带有单列 id 的表 my_ids.接下来，我有一个表值函数 fn_getMatches(id).我想要的是遍历表 my_ids 和每个 id 调用函数 fn_getMatches(id) 并将所有结果汇总到一个表中.如果没有显式循环，我该怎么做?

I have a table my_ids with single column id. Next, I have a table-valued function fn_getMatches(id). What I want is to iterate through table my_ids and for each id call function fn_getMatches(id) and aggregate all results in one table. How do I do that without explicit loop?

我试过了:

select *
from my_ids ids
     full outer join fn_getMatches(ids.id) on 1=2
where ids.id is null

但它返回:

消息 4104，级别 16，状态 1，第 11 行
无法绑定多部分标识符ids.id".

Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "ids.id" could not be bound.

推荐答案

不知道函数是做什么的，或者你期望得到什么结果，不妨试试:

Having no idea what the function does, or what results you expect, maybe try:

select * -- name your columns!
from dbo.my_ids AS ids -- use schema prefix!
cross apply dbo.fn_getMatches(ids.id); -- use schema prefix!

从您最初的尝试中删除了 WHERE 子句和 ON 条件.

Removed the WHERE clause and the ON criteria from your initial attempt.

这篇关于正确使用表值函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！