SQL 将表值函数与表连接，其中表字段是函数输入 [英] SQL Join table-valued function with table where table field is a function input

问题描述

我有一个名为 fn_SplitCommaSep 的表值函数，它用逗号分隔文本字段(从 'a, b, c' 到 3 行:a b c)

I have a table-valued function called fn_SplitCommaSep, which comma-separates a text field (from 'a, b, c' to 3 rows: a b c)

如何将其加入表格，将表格列作为输入?

How can I join this to a table, taking a table column as an input?

为此，假设表 MyTable 有 2 列，Id 和 TextWithCommas，并且表值函数 fn_SplitCommaSep 生成一列名为 TextWithoutComma

For the purpose of this, say the table MyTable has 2 columns, Id and TextWithCommas, and that table-valued function fn_SplitCommaSep produces one column called TextWithoutComma

例如.类似其中之一

select fs.TextWithoutComma
  from fn_SplitCommaSep(select mt.TextWithCommas from MyTable) fs 

或

select fs.TextWithoutComma, mt.Id
  from MyTable mt
    inner join fn_SplitCommaSep(mt.TextWithCommas) fs on (something)

推荐答案

在 DB 中存储逗号分隔值，请查看 应用

Storing comma-separated values in a DB aside, take a look at APPLY

比如:

SELECT fs.TextWithoutComma, mt.Id 
FROM   MyTable mt 
    CROSS APPLY fn_SplitCommaSep(mt.TextWithCommas) AS fs

这篇关于SQL 将表值函数与表连接，其中表字段是函数输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！