如何在Oracle中查找多组连续整数？

Question

我正在使用oracle 10g。

我的(简化的)表定义是

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 |

Answer 1

您可以使用

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 Fiddle