从SQL/Oracle中的一系列数字中查找范围 [英] Find ranges from a series of numbers in SQL/Oracle

问题描述

我有一个包含一系列数字的表.1,2,3,4,5,11,12,13,14,15,101,102,103,104,105,510,511,512,513,515,516,517.
我想要一个SQL查询，过程或函数，以便获得以下格式的范围.

I have a table containing a series of numbers 1,2,3,4,5,11,12,13,14,15,101,102,103,104,105,510,511,512,513,515,516,517.
I want an SQL Query, Procedure or Function so that I can get the ranges in the following formats.

从-到:

1-5
11-15
101-105
510-517

OR

1-5, 11-15, 101-105, 510-517

推荐答案

您可以使用 ROW_NUMBER 分析函数来完成.参见

You could do it using ROW_NUMBER analytic function. See Find range of consecutive values in a sequence of numbers or dates.

例如

范围

SQL> with data(num) as(
  2      select  1 from dual union
  3      select  2 from dual union
  4      select  3 from dual union
  5      select  5 from dual union
  6      select  6 from dual union
  7      select  7 from dual union
  8      select 10 from dual union
  9      select 11 from dual union
 10      select 12 from dual union
 11      select 20 from dual
 12  )
 13  select min(num)||'-'|| max(num) as "range"
 14  from (select num,
 15           num-Row_Number() over(order by num)
 16           as rn
 17           from data)
 18  group by rn
 19  order by min(num);

range
-------------------------------------------------
1-3
5-7
10-12
20-20

SQL>

列表

SQL> with data(num) as(
  2      select  1 from dual union
  3      select  2 from dual union
  4      select  3 from dual union
  5      select  5 from dual union
  6      select  6 from dual union
  7      select  7 from dual union
  8      select 10 from dual union
  9      select 11 from dual union
 10      select 12 from dual union
 11      select 20 from dual
 12  )
 13  SELECT listagg(range, ',') WITHIN GROUP(
 14  ORDER BY min_num) AS "list"
 15  FROM
 16    (SELECT MIN(num) min_num,
 17      MIN(num)
 18      ||'-'
 19      || MAX(num) range
 20    FROM
 21      (SELECT num, num-Row_Number() over(order by num) AS rn FROM DATA
 22      )
 23    GROUP BY rn
 24    );

list
-------------------------------------------------------------------------
1-3,5-7,10-12,20-20

SQL>

更新 OP希望使用PL/SQL中的解决方案将列表存储在PL/SQL变量中.

Update OP wants a solution in PL/SQL to store the list in a PL/SQL variable.

设置

SQL> CREATE TABLE t AS
  2  SELECT *
  3  FROM
  4    ( WITH data(num) AS
  5    ( SELECT 1 FROM dual
  6    UNION
  7    SELECT 2 FROM dual
  8    UNION
  9    SELECT 3 FROM dual
 10    UNION
 11    SELECT 5 FROM dual
 12    UNION
 13    SELECT 6 FROM dual
 14    UNION
 15    SELECT 7 FROM dual
 16    UNION
 17    SELECT 10 FROM dual
 18    UNION
 19    SELECT 11 FROM dual
 20    UNION
 21    SELECT 12 FROM dual
 22    UNION
 23    SELECT 20 FROM dual
 24    )
 25  SELECT * FROM DATA);

Table created.

PL/SQL块

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    v_list VARCHAR2(100);
  3  BEGIN
  4    SELECT listagg(RANGE, ',') WITHIN GROUP(
  5    ORDER BY min_num)
  6    INTO v_list
  7    FROM
  8      (SELECT MIN(num) min_num,
  9        MIN(num)
 10        ||'-'
 11        || MAX(num) range
 12      FROM
 13        (SELECT num, num-Row_Number() over(order by num) AS rn FROM t
 14        )
 15      GROUP BY rn
 16      );
 17    dbms_output.put_line(v_list);
 18  END;
 19  /
1-3,5-7,10-12,20-20

PL/SQL procedure successfully completed.

SQL>

这篇关于从SQL/Oracle中的一系列数字中查找范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！