获取两列之间的数字列表

本文介绍了获取两列之间的数字列表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在oracle中有一个table1(startnumber，endnumber);

I have a table1(startnumber, endnumber) in oracle;

现在我需要从table1中选择所有数字，它们位于startnumber和endnumber之间

Now I need to select all the numbers lies in between startnumber and endnumber from table1

数据例如

    table1
   startnumber      endnumber
     10                15
    18                 22
    34                 50

Now I need the result set as:

10,11,12,13,14,15,18,19,20,21,22,34,35..... and so on

我该怎么做

推荐答案

如果您希望用逗号分隔值，则可以这样做(或者如果在11gr2上使用 listAgg )

if you wanted the values comma separated you could do as such (or if on 11gr2 use listAgg)

WITH NUMS AS(
     SELECT 10 STARTNR  , 15 ENDNR  FROM DUAL UNION
    SELECT 18 STARTNR  , 22 ENDNR  FROM DUAL UNION
    SELECT 34 STARTNR  , 50 ENDNR  FROM DUAL )       , 
 NUMSBETWEEN AS(
     SELECT distinct (level + startnr)-1 seq , startnr, endnr
       FROM NUMS
      CONNECT BY (LEVEL +STARTNR ) <= ENDNR+1
      )
SELECT startnr,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(seq,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS seq
FROM   (SELECT startnr,
               seq,
               ROW_NUMBER() OVER (PARTITION BY startnr ORDER BY seq) AS curr,
               ROW_NUMBER() OVER (PARTITION BY startnr ORDER BY seq) -1 AS prev
        FROM   numsBetween)
GROUP BY startnr
CONNECT BY prev = PRIOR curr AND startnr = PRIOR startnr
START WITH CURR = 1;

STARTNR SEQ

10 10,11,12,13,14,15
18 18,19,20,21,22
34 34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50

这篇关于获取两列之间的数字列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！