一张表 a
值
1
2
3
4
10
15
16
20要求得结果
1-4
10-10
13-13
15-16
20-20一条sql 如何实现
值
1
2
3
4
10
15
16
20要求得结果
1-4
10-10
13-13
15-16
20-20一条sql 如何实现
解决方案 »
- oracle 正则表达式 不包含 某个字符串
- oracle “;” 提示无效字符的问题
- 求解释~~~~~~~~~~
- 请教个SQL问题 速度来高手解答 非常急
- recover提示找不到归档日志,怎么处理阿
- 怎样在Oracle9i的基础上访问Oracle8数据库?
- ORACLE數據庫中表已經存在且有記錄﹐我要修改結構(通過SQL語句)...
- 一条SQL语句创建java对象
- 更新一列的内容至另一列
- 关于oracle 表分区问题,假如某省每个县的数据建一个分区,要建一百多个分区,会不会影响效率
- A Database develoter interview test
- 请问如何在oracle 声明一个存放变量的类
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
)