問題描述
我有一個帶有單列 id 的表 my_ids.接下來，我有一個表值函數(shù) fn_getMatches(id).我想要的是遍歷表 my_ids 和每個 id 調(diào)用函數(shù) fn_getMatches(id) 并將所有結(jié)果匯總到一個表中.如果沒有顯式循環(huán)，我該怎么做?
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，狀態(tài) 1，第 11 行
無法綁定多部分標(biāo)識符ids.id".
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "ids.id" could not be bound.
推薦答案
不知道函數(shù)是做什么的，或者你期望得到什么結(jié)果，不妨試試:
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.
這篇關(guān)于正確使用表值函數(shù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！