sql - 逗号分隔字段上的表值函数 (TVF)

Question

我有这张表:

create table Test (Value varchar(111))
insert Test select 'a,b,c'

我想创建一个表值函数，我在其中传递 Test.Value 并返回下表:

其中 Value 来自 Test 表，而 Item 值以这种方式生成:第一项本身就是整个值(在示例中它由三个逗号分隔值组成)，一旦没有任何三个逗号分隔值，我们从左到右查找两个逗号分隔值。

我们严格从左到右，所以不需要像 a,c 或 b,a 这样的项目。然后我们最后找到一个逗号分隔值，即 a、b 和 c。

ItemLayer 只是一个正在处理的图层。显然，分隔符应该是逗号，tvf 应该返回Item 和ItemLayer。我认为查询应该看起来像这样:

SELECT *
FROM Test t
CROSS JOIN fn_getItemsFromValues(t.Value) f

我认为应该有某种递归 CTE，但我不知道如何实现。

如果 Value 为 'a,b,c,d'，则输出如下:

我使用的是 SQL Server 2017。我试过这个，但我卡住了。这里有些不对劲。

DECLARE @data VARCHAR(100) = 'a,b,c'
;WITH CTE AS
(
    SELECT @data TXT, LEFT(@data,1) Col1
    UNION ALL
    SELECT STUFF(TXT,1,1,'') TXT, LEFT(TXT,1) Col1 FROM CTE
    WHERE LEN(TXT) > 0
)
select Col1,txt from CTE

Answer 1

我不确定为什么 a,c 不在列表中。但是您可以通过拆分字符串然后使用递归 CTE 来生成所有组合:

with t as (
      select t.value, convert(varchar(max), s.value) as val
      from test t cross apply
           string_split(t.value, ',') s
     ),
     cte as (
      select t.value, t.val as str, t.val as lastval, 1 as lev
      from t
      union all
      select cte.value, concat(cte.str, ',', t.val), t.val, lev + 1
      from cte join
           t
           on cte.value = t.value and cte.lastval < t.val
     )
select cte.*, dense_rank() over (order by lev desc) as itemlayer
from cte;

Here是一个数据库<> fiddle 。

编辑:

我认为解决“相邻”限制的最佳方法实际上是对以前的解决方案进行微调。这为元素添加了一个定位器，并且只允许在递归步骤中引入下一个元素:

with t as (
      select t.value, convert(varchar(max), s.value) as val,
             row_number() over (order by charindex(',' + s.value + ',', ',' + t.value + ',')) as ind
      from test t cross apply
           string_split(t.value, ',') s
     ),
     cte as (
      select t.value, t.val as str, t.ind, 1 as lev
      from t
      union all
      select cte.value, concat(cte.str, ',', t.val), t.ind, lev + 1
      from cte join
           t
           on cte.value = t.value and t.ind = cte.ind + 1
     )
 select cte.*, dense_rank() over (order by lev desc) as itemlayer
from cte;

如果您有重复的元素，这将不起作用。如果是这样，请提出一个新问题。