如何在Oracle中查找连续整数组? [英] How to find groups of sequential integers in Oracle?

问题描述

我正在使用oracle 10g.

I am using oracle 10g.

我的(简化的)表定义是

My (simplified) table definition is

CREATE TABLE Student
    ("Rno" INT PRIMARY KEY)
;

其中包含以下行

| RNO |
|-----|
|   1 |
|   2 |
|   3 |
|   6 |
|   8 |
|   9 |
|  12 |
|  13 |
|  14 |
|  18 |

如何生成以下结果集?

|                 RESULT |
|------------------------|
| 1-3, 6, 8-9, 12-14, 18 |

推荐答案

您可以使用

SELECT LISTAGG("Txt", ', ') WITHIN GROUP (ORDER BY "Rno") "Result"
FROM   (SELECT CASE
                 WHEN MIN("Rno") = MAX("Rno") THEN CAST(MIN("Rno") AS VARCHAR2(11))
                 ELSE MIN("Rno")  || '-'  || MAX("Rno")
               END        AS "Txt",
               MIN("Rno") AS "Rno"
        FROM   (SELECT ROW_NUMBER() OVER (ORDER BY "Rno") - "Rno" AS "Grp",
                       "Rno"
                FROM   Student)
        GROUP  BY "Grp") 

SQL提琴

这篇关于如何在Oracle中查找连续整数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！