情况描述：
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 dualconnect by level<18)where rn not in(3,4,6,8)
------解决方案--------------------
- 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-rnORDER BY gd;