SQL 在多行上执行表值函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/454945/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute table-valued function on multiple rows?
提问by S?ren Kuklau
Given a table-valued function such as dbo.Split()from "T-SQL: Opposite to string concatenation - how to split string into multiple records", how do I pass multiple rows as arguments?
给定一个表值函数，例如dbo.Split()来自"T-SQL: Opposite to string concatenation - how to split string into multiple records"，如何将多行作为参数传递？
This works:
这有效：
SELECT *
FROM dbo.Split
(',', (SELECT myColumn FROM Stuff WHERE id = 22268))
WHERE ISNULL(s,'') <> ''
It returns:
它返回：
pn s
----------- -----------
1 22351
2 22354
3 22356
4 22357
5 22360
But this does not:
但这不会：
SELECT *
FROM dbo.Split
(',', (SELECT myColumn FROM Stuff))
WHERE ISNULL(s,'') <> ''
Nor does this:
这也没有：
SELECT * FROM dbo.Split_temp(',', myColumn), Stuff
The docssay:
文档说：
When a user-defined function that returns a table is invoked in the FROM clause of a subquery, the function arguments cannot reference any columns from the outer query.
当在子查询的 FROM 子句中调用返回表的用户定义函数时，函数参数不能引用外部查询中的任何列。
The sort of result set I'm looking for would look something like:
我正在寻找的那种结果集看起来像：
id pn s
----------- ----------- -----------
22268 1 22351
22268 2 22354
22268 3 22356
22268 4 22357
22268 5 22360
24104 1 22353
24104 2 22355
24104 3 22356
24104 4 22358
24104 5 22360
24104 6 22362
24104 7 22364
.
.
.
Is there any way at all (aside from, of course, a cursor) to accomplish this?
有没有什么办法（当然，除了游标）来实现这一点？
(edit)
（编辑）
As requested by MarlonRibunal, a sample table to produce the above result looks like:
根据 MarlonRibunal 的要求，生成上述结果的示例表如下所示：
id myColumn
----------- -------------------------------------------
22268 22351,22354,22356,22357,22360,
24104 22353,22355,22356,22358,22360,22362,22364,
idis an int; myColumnis a varchar(max).
id是int; myColumn是一个varchar(max)。
回答by Cade Roux
OUTER APPLY:
OUTER APPLY：
SELECT Stuff.id
,Results.pn
,Results.s
FROM stackoverflow_454945 AS Stuff
OUTER APPLY dbo.Split(',', Stuff.myColumn) AS Results
WHERE ISNULL(Results.s,'') <> ''
回答by cmsjr
You could use the COALESCE concatenation behavior
您可以使用 COALESCE 串联行为
declare @split varchar(max)
set @split = ''
select @split = @split + Coalesce(myColumn + ',' , ' ')
from Stuff
WHERE id = 22268
select * from dbo.Split(',', @Left(@split,len(@split) -1))
I don't know that that would be any better than using a cursor.
我不知道这会比使用游标更好。
