查找从单个串联值字符串分成几行的最低和最高值 [英] Find lowest and highest values split into rows from a single string of concatenated values

问题描述

这是我的问题这里：在其中，我为 uzi 提供的问题得到了很好的答案。但是，我注意到一个新的公司 Company3 也使用了单个数据点，例如帐户6000，它不遵循以前的公司的方式，这使得uzi的递归cte不适用。

This is a follow-up to my question here: in which I got an excellent answer for that question provided by uzi. I however noticed that a new Company, Company3 also used single data Points, such as account 6000 which does not follow the manner of the previous companies which makes uzi's recursive cte not applicable.

因此，我觉得有必要更改问题，但是我相信这种复杂性会产生一个新问题，而不是对我以前的问题进行编辑，因为解决方案的巨大影响。

As such I feel like it is required to alter the question, but I Believe that this complication would issue a new question rather than an edit on my previous one due to having a great impact of the solution.

我需要从以这种方式存储数据的Excel工作簿中读取数据：

I need to read data from an Excel workbook, where data is stored in this manner:

Company       Accounts
Company1      (#3000...#3999)
Company2      (#4000..#4019)+(#4021..#4024)
Company3      (#5000..#5001)+#6000+(#6005..#6010)

I认为由于某些公司（例如 Company3 ）具有我需要的单个帐户值（例如＃6000 ），在此步骤中，创建以下外观的结果集：

I believe that due to some companies, such as Company3 having single values of accounts such as #6000 that I need to, in this step, create a result set of the following appearence:

Company       FirstAcc LastAcc
Company1      3000     3999
Company2      4000     4019
Company2      4021     4024
Company3      5000     5001
Company3      6000     NULL
Company3      6005     6010

然后，我将使用此表并将其与仅包含整数的表联接以获取最终表的外观，例如链接问题中的那个。

I will then use this table and JOIN it with a table of only integers to get the appearance of the final table such as the one in my linked question.

有人有什么想法吗？

推荐答案

良好的t-sql拆分器功能使此操作非常简单。我建议 delimitedSplit8k 。这也将比递归CTE更好。首先是示例数据：

A good t-sql splitter function makes this quite simple; I suggest delimitedSplit8k. This will perform significantly better than a recursive CTE too. First the sample data:

-- your sample data
if object_id('tempdb..#yourtable') is not null drop table #yourtable;
create table #yourtable (company varchar(100), accounts varchar(8000));
insert #yourtable values ('Company1','(#3000...#3999)'),
('Company2','(#4000..#4019)+(#4021..#4024)'),('Company3','(#5000..#5001)+#6000+(#6005..#6010)');

和解决方案：

select 
  company, 
  firstAcc = max(case when split2.item not like '%)' then clean.Item end),
  lastAcc  = max(case when split2.item     like '%)' then clean.Item end)
from #yourtable t
cross apply dbo.delimitedSplit8K(accounts, '+') split1
cross apply dbo.delimitedSplit8K(split1.Item, '.') split2
cross apply (values (replace(replace(split2.Item,')',''),'(',''))) clean(item)
where split2.item > ''
group by split1.Item, company;

结果：

company   firstAcc   lastAcc
--------- ---------- --------------
Company1  #3000      #3999
Company2  #4000      #4019
Company2  #4021      #4024
Company3  #6000      NULL
Company3  #5000      #5001
Company3  #6005      #6010

这篇关于查找从单个串联值字符串分成几行的最低和最高值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！