How to find groups of sequential integers in Oracl

问题:

I am using oracle 10g.

My (simplified) table definition is

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

Which contains the following rows

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

How can I produce the following result set?

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

回答1:

You can use

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