在多行上执行表值函数? [英] Execute table-valued function on multiple rows?

问题描述

给定一个表值函数，例如 "T-SQL:与字符串连接相反 - 如何将字符串拆分为多条记录"，如何将多行作为参数传递?

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?

这有效:

SELECT *
FROM dbo.Split
  (',', (SELECT myColumn FROM Stuff WHERE id = 22268))
WHERE ISNULL(s,'') <> ''

它返回:

pn          s
----------- -----------
1           22351
2           22354
3           22356
4           22357
5           22360

但这不会:

SELECT *
FROM dbo.Split
  (',', (SELECT myColumn FROM Stuff))
WHERE ISNULL(s,'') <> ''

这也不行:

SELECT * FROM dbo.Split_temp(',', myColumn), Stuff

文档说:

当在子查询的 FROM 子句中调用返回表的用户定义函数时，函数参数不能引用外部查询中的任何列.

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.

我正在寻找的结果集类似于:

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?

(编辑)

根据 MarlonRibunal 的要求，生成上述结果的示例表如下所示:

As requested by MarlonRibunal, a sample table to produce the above result looks like:

id          myColumn
----------- -------------------------------------------
22268       22351,22354,22356,22357,22360,
24104       22353,22355,22356,22358,22360,22362,22364,

id 是一个 int；myColumn 是一个 varchar(max).

id is an int; myColumn is a varchar(max).

推荐答案

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,'') <> ''

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