看到个纠结的题目，SQl高手们进来进来尽情发挥了哇哇哇哇！！！

情况描述：
select id from table XXX 可以获取到
1
2
5
7
9
10
11
12
13
14
15
16
17
直接用sql来编写使
select 各种天才想法 id from table XXX得到结果为
1-2,5,7,9-17这么个结果
没有思路哇，高手给大家表演下。~~~~~~~~~鼓掌！！！

SQL code

--写个……………………
select * 
from (select level rn 
from dual
connect by level<18)
where rn not in(3,4,6,8)

结果不是想要的哦，是把连着的用1-10类似的连起来，没连的单独读出来，是在一个结果中用逗号连接着

顶一下，路过的高手们发挥下了啊

SQL code

[SYS@orcl] SQL>WITH xxx AS(
  2  SELECT 1  ID FROM DUAL UNION ALL
  3  SELECT 2  ID FROM DUAL UNION ALL
  4  SELECT 5  ID FROM DUAL UNION ALL
  5  SELECT 7  ID FROM DUAL UNION ALL
  6  SELECT 9  ID FROM DUAL UNION ALL
  7  SELECT 10 ID FROM DUAL UNION ALL
  8  SELECT 11 ID FROM DUAL UNION ALL
  9  SELECT 12 ID FROM DUAL UNION ALL
 10  SELECT 13 ID FROM DUAL UNION ALL
 11  SELECT 14 ID FROM DUAL UNION ALL
 12  SELECT 15 ID FROM DUAL UNION ALL
 13  SELECT 16 ID FROM DUAL UNION ALL
 14  SELECT 17 ID FROM DUAL)
 15  SELECT WM_CONCAT(DECODE(MIID, MAID, TO_CHAR(MAID), MIID || '-' || MAID))
 16    FROM (SELECT MIN(ID) MIID, MAX(ID) MAID
 17            FROM (SELECT ID, ID - DENSE_RANK() OVER(ORDER BY ID) ROW_ FROM XXX)
 18           GROUP BY ROW_
 19           ORDER BY MIID);

WM_CONCAT(DECODE(MIID,MAID,TO_CHAR(MAID),MIID||'-'||MAID))
--------------------------------------------------------------------------------
1-2,5,7,9-17

老题目了..
SQL code

with xxx as
 (select rownum id
    from dual
  connect by rownum < 18
  minus (select 3
          from dual
        union all
        select 4
          from dual
        union
        select 6
          from dual
        union
        select 8 from dual))
--以上是测试数据
select cast (wm_concat(id) as varchar2(4000)) id
  from (select case count(1)
                 when 1 then
                  to_char(min(id))
                 else
                  min(id) || '-' || max(id)
               end id
          from (select id from xxx order by id)
         group by rownum - id
         order by id)

SQL code

SELECT MIN(ID)||'-'||MAX(ID) AS gd FROM (
SELECT ID,ROWNUM rn FROM vnum ORDER BY ID)
GROUP BY ID-rn
ORDER BY gd;