SQL: create sequential list of numbers from variou

问题:

I'm stuck on this SQL problem.

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).

For example, here are the first several rows:

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

So I need an output to look something like:

2
3
4
22
23
127
128

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

Thanks!

回答1:

;with a as
(
select prevdoc + 1 col, exdiff
from <table> where exdiff > 0
union all
select col + 1, exdiff - 1
from a
where exdiff > 1
)
select col

回答2:

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;

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;

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

回答3:

If the numbers are not too large, then you can use the following trick in most databases:

select t.exdiff + seqnum
from t join
     (select row_number() over (order by column_name) as seqnum
      from INFORMATION_SCHEMA.columns
     ) nums
     on t.exdiff <= seqnum

The use of INFORMATION_SCHEMA columns in the subquery is arbitrary. The only purpose is to generate a sequence of numbers at least as long as the maximum exdiff number.

This approach will work in any database that supports the ranking functions. Most databases have a database-specific way of generating a sequence (such as recursie CTEs in SQL Server and CONNECT BY in Oracle).