Oracle-将行中的值转换为范围

问题描述

有没有什么技术可以允许这样的行设置

Are there any techniques that would allow a row set like this

WITH
base AS
(
    SELECT  1 N FROM DUAL UNION ALL
    SELECT  2 N FROM DUAL UNION ALL
    SELECT  3 N FROM DUAL UNION ALL

    SELECT  6 N FROM DUAL UNION ALL
    SELECT  7 N FROM DUAL UNION ALL

    SELECT 17 N FROM DUAL UNION ALL
    SELECT 18 N FROM DUAL UNION ALL
    SELECT 19 N FROM DUAL UNION ALL

    SELECT 21 N FROM DUAL
)
SELECT  a.N
FROM base a

产生结果

 1     3
 6     7
17    19
21    21

实际上是行到范围的操作.我正在Oracle Land上玩，希望得到任何建议.

It is in effect a rows to ranges operation.I'm playing in Oracle Land, and would appreciate any suggestions.

推荐答案

我认为可以对此进行改进，但是可以:

I feel like this can probably be improved on, but it works:

WITH base AS  (
    SELECT  1 N FROM DUAL UNION ALL
    SELECT  2 N FROM DUAL UNION ALL
    SELECT  3 N FROM DUAL UNION ALL
    SELECT  6 N FROM DUAL UNION ALL
    SELECT  7 N FROM DUAL UNION ALL
    SELECT 17 N FROM DUAL UNION ALL
    SELECT 18 N FROM DUAL UNION ALL
    SELECT 19 N FROM DUAL UNION ALL
    SELECT 21 N FROM DUAL
)
, lagged AS
(
    SELECT n, LAG(n) OVER (ORDER BY n) lag_n FROM base
)
, groups AS
(
    SELECT n, row_number() OVER (ORDER BY n) groupnum
      FROM lagged
      WHERE lag_n IS NULL OR lag_n < n-1
)
, grouped AS
(
    SELECT n, (SELECT MAX(groupnum) FROM groups
                 WHERE groups.n <= base.n
              ) groupnum
      FROM base
)
SELECT groupnum, MIN(n), MAX(n)
  FROM grouped
  GROUP BY groupnum
  ORDER BY groupnum

这篇关于Oracle-将行中的值转换为范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！