试一下这个SQL语句 select min(id)|| ',' ||max(id) from (select id,rownum rn,id-rownum diff from cc) group by diff order by diff
看出来了,5楼的sql碰到初始数据是乱序的就不行了。还是看我这个: SELECT MIN (ID) || '-' || MAX (ID) FROM (SELECT ID, RANK () OVER (ORDER BY ID) rn, ID - RANK () OVER (ORDER BY ID) diff FROM testa) GROUP BY diff ORDER BY diff;测试如下:CREATE TABLE TESTA ( ID INTEGER );Insert into TESTA (ID) Values (2); Insert into TESTA (ID) Values (3); Insert into TESTA (ID) Values (4); Insert into TESTA (ID) Values (10); Insert into TESTA (ID) Values (20); Insert into TESTA (ID) Values (1); Insert into TESTA (ID) Values (15); COMMIT;SELECT MIN (ID) || '-' || MAX (ID) FROM (SELECT ID, RANK () OVER (ORDER BY ID) rn, ID - RANK () OVER (ORDER BY ID) diff FROM testa) GROUP BY diff ORDER BY diff;结果: 1-4 10-10 15-15 20-20
可以不用那么多的分析函数也能实现任意顺序的数据的查询:select min(id)||','||max(id) from (select rownum rn,id,id-rownum diff from (select id from cc order by id) ) group by diff order by diff
这个问题用三个分析函数+一个子查询很好解决,以 fenixshadow 创建的表为例: SELECT DISTINCT MIN(PREV) OVER (PARTITION BY DIFF ORDER BY DIFF) AS PREV, MAX(PREV) OVER (PARTITION BY DIFF ORDER BY DIFF) AS NEXT FROM ( SELECT ID AS PREV, ID - RANK () OVER (ORDER BY ID) DIFF FROM TESTA )
不过性能上还是推荐 fenixshadow 的SQL, 在这种情况下GROUP BY 比 PARTITION BY要好:)
select min(id)|| ',' ||max(id) from
(select id,rownum rn,id-rownum diff from cc)
group by diff
order by diff
SELECT MIN (ID) || '-' || MAX (ID)
FROM (SELECT ID, RANK () OVER (ORDER BY ID) rn,
ID - RANK () OVER (ORDER BY ID) diff
FROM testa)
GROUP BY diff
ORDER BY diff;测试如下:CREATE TABLE TESTA
(
ID INTEGER
);Insert into TESTA
(ID)
Values
(2);
Insert into TESTA
(ID)
Values
(3);
Insert into TESTA
(ID)
Values
(4);
Insert into TESTA
(ID)
Values
(10);
Insert into TESTA
(ID)
Values
(20);
Insert into TESTA
(ID)
Values
(1);
Insert into TESTA
(ID)
Values
(15);
COMMIT;SELECT MIN (ID) || '-' || MAX (ID)
FROM (SELECT ID, RANK () OVER (ORDER BY ID) rn,
ID - RANK () OVER (ORDER BY ID) diff
FROM testa)
GROUP BY diff
ORDER BY diff;结果:
1-4
10-10
15-15
20-20
(select rownum rn,id,id-rownum diff from
(select id from cc order by id)
)
group by diff
order by diff
常青松
等 级:
发表于:2008-01-19 08:51:3313楼 得分:0
其实一个分析函数也相当于一个嵌套,
Oracle没这个说法吧? 如有, 能提供一点资料大家一起学习学习吗?
SELECT DISTINCT MIN(PREV) OVER (PARTITION BY DIFF ORDER BY DIFF) AS PREV,
MAX(PREV) OVER (PARTITION BY DIFF ORDER BY DIFF) AS NEXT
FROM (
SELECT ID AS PREV, ID - RANK () OVER (ORDER BY ID) DIFF
FROM TESTA
)