如何处理嵌入列中的列表和范围？

Question

我无法控制我要处理的数据的格式。当然，我可以使用脚本语言来处理数据库之外的以下问题，但我想避免这种情况，因为我正在处理的数据量很大，而且我想消除手动的必要性步骤。

简而言之，我有一张清单。列表可能包含单个 3 位数字符串、多个 3 位数字符串、一系列 3 位数字符串（例如 012-018）或多个 3 位数字符串和范围3 位数字的字符串。例如：

drop table list;
drop table lists;

create table lists (id varchar, vals varchar);

insert into lists values('A', '001,003-005');
insert into lists values('B', '008-007');
insert into lists values('C', '010, 011, 012');
insert into lists values('D', '011-013, 016-018, 020');

我知道、我知道。

我想把它变成下表：

create table list (id varchar, val varchar);

A   001
A   003
A   004
A   005
B   008
B   007
C   010
C   011
C   012
D   011
D   012
D   013
D   016
D   017
D   018
D   020

有没有办法在 SQL 中做到这一点？

Answer 1

由于您没有使用特定的 RDBMS 标记您的问题，因此我必须笼统地回答。

SQL 本身不提供您正在寻找的基本操作，这基本上是字符串拆分。这意味着您必须自己编写，或者使用已在线发布的许多之一。

不过，您的数据范围使问题变得有点复杂。这意味着您的过程将如下所示：

1. 将数据插入临时/内存表并按程序迭代它（或者，使用游标执行相同操作）
2. 对于集合中的每个记录，提取非-标准化字符串数据并用','分割。
3. 对于分割数据中的每个元素，您必须用 '-' 分割（对于非范围元素，应该返回一个结果） 。
4. 如果您的第二次拆分（在 '-' 上）产生一个结果，则它是一条记录，您可以将其插入到最终目标中。如果它产生两个结果，那么它是一个范围，您必须从开始到结束进行迭代（使用该拆分的元素 1 和 2）并将记录插入到最终目标

评论后编辑

不幸的是，我对 PROC SQL 或 SAS 不熟悉，所以我无法为此提供具体的解决方案。我可以在下面发布一些关于 SQL Server T-SQL 的内容，希望能够帮助您入门。

declare @results table (idx int identity(1, 1), id varchar(5), data varchar(max))
declare @elements table (idx int identity(1, 1), element varchar(25))
declare @range table (idx int identity(1, 1), element varchar(25))

insert into @results (id, data)
select
    your_id,
    your_data

from your_source

declare @i int
declare @cnt int

declare @j int
declare @cnt2 int

declare @element varchar(25)

declare @first int
declare @second int

declare @start int
declare @end int

declare @id varchar(5)
declare @data varchar(max)

select @i = min(idx) - 1, @cnt = max(idx) from @results

while @i < @cnt
begin
    select @i = @i + 1

    select @id = id, @data = data from @results where idx = @i

    delete @elements

    insert into @elements (element) 
    select
        element

    from split(@data, ',')

    select @j = min(idx) - 1, @cnt2 = max(idx) from @elements

    while @j < @cnt2 
    begin
        select @j = @j + 1

        select @element = element from @elements where idx = @j

        delete @range

        insert into @range (element)
        select
            element

        from split(@element, '-')

        select @first = min(idx), @second = max(idx) from @range

        if @first = @second --single element
            insert into final_destination (id, value)
            select
                @id,
                element

            from @range
        else if @second - @first = 1 -- two elements, as desired
        begin
            select @start = convert(int, element) - 1 from @range where idx = @first
            select @end = convert(int, element) from @range where idx = @second

            while @start < @end
            begin
                select @start = @start + 1

                insert into final_destination (id, value)
                values (@id, @start)
            end
        end
        else -- error condition, bad input
    end
end