SQL:从各个起点创建顺序编号列表 [英] SQL: create sequential list of numbers from various starting points

问题描述

我一直在解决这个SQL问题.

I'm stuck on this SQL problem.

我有一列是起点(prevdoc)的列，而另一列则列出了在起点(exdiff)之后我需要多少个序号.

I have a column that is a list of starting points (prevdoc), and anther column that lists how many sequential numbers I need after the starting point (exdiff).

例如，以下是前几行:

prevdoc | exdiff
----------------    
1       | 3
21      | 2
126     | 2

所以我需要一个输出看起来像这样:

So I need an output to look something like:

2
3
4
22
23
127
128

我不知道该从哪里开始.谁能为我提供有关此解决方案的SQL代码的建议?

I'm lost as to where even to start. Can anyone advise me on the SQL code for this solution?

谢谢！

推荐答案

如果您的exdiff将是一个小数字，则可以使用SELECT..UNION ALL组成一个虚拟数字表，如下所示并加入该表:

If your exdiff is going to be a small number, you can make up a virtual table of numbers using SELECT..UNION ALL as shown here and join to it:

select prevdoc+number
from doc
join (select 1 number union all
      select 2 union all
      select 3 union all
      select 4 union all
      select 5) x on x.number <= doc.exdiff
order by 1;

我已提供5个，但您可以根据需要进行扩展.您尚未指定DBMS，但每个数据库中都有一个序列号源，例如在SQL Server中，您可以使用:

I have provided for 5 but you can expand as required. You haven't specified your DBMS, but in each one there will be a source of sequential numbers, for example in SQL Server, you could use:

select prevdoc+number
from doc
join master..spt_values v on
   v.number <= doc.exdiff and
   v.number >= 1 and
   v.type = 'p'
order by 1;

master..spt_values表包含0到2047之间的数字(按type ='p'过滤时).

The master..spt_values table contains numbers between 0-2047 (when filtered by type='p').

这篇关于SQL:从各个起点创建顺序编号列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！