在逗号分隔的字段上使用表值函数(TVF)

Question

在逗号分隔的字段上使用表值函数(TVF)

sqlsql-servert-sqlcommon-table-expression

3

我有这个表格：

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

- Eric Klaus

请编辑您的问题以：1）指定您正在使用的SQL Server版本（在这种情况下很重要），并2）展示您已经编写的UDF代码，并解释为什么它对您无效。 - AlwaysLearning

@AlwaysLearning，完成 - Eric Klaus

@EricKlaus 为什么你没有将 'a,c' 作为一种组合呢？ - Gordon Linoff

@GordonLinoff，不需要。我需要严格从左到右进行，没有任何跳过。在a、c中，我们跳过了b，这就是为什么我们没有它的原因。 - Eric Klaus

2个回答

0

这是我得到的：

drop function if exists fn_getItemsFromValues 
go
create function fn_getItemsFromValues (@val varchar(100))
returns @ret table
(
[value] varchar (100)
,item varchar (100)
,ItemLayer int
) 
as
begin
    declare
        @v varchar (1000)=@val
        ,@i int=1
        ,@J int=1
        ,@v_n varchar (1000)=''

    set @v=@val
    declare
        @t table (val varchar (1000))


    while @i<=len(@v)
    begin
        set @j=1
        while @i+@J-1<=len(@v)
        begin
            set @v_n=substring (@v, @i, @j)
            if len(@v_n)>1
                set @v_n=isnull(nullif(LEFT(@v_n,1),',') ,'') +substring (@v_n,2, len(@v_n)-2)+isnull(nullif(right(@v_n,1),',') ,'')

            insert into @t
            select @v_n where @v_n!=','
            set @j=@j+1
        end
    set @i=@i+1
    end

    insert into @ret
    select @v, val, count(ln) over (partition by ln) 
    from (
        select distinct val, len(val) ln from @t
        )t
    return
end

--select * from  dbo.fn_getItemsFromValues ('a,b,c,d') order by 3

- dzhukov

需要解释一下。 - Peter Mortensen

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

我不确定为什么列表中没有 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;

这里是一个db<>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;

如果元素有重复，这种方法就不起作用了。如果存在这种情况，请提出新的问题。