日期:2014-05-17 浏览次数:20634 次
SQL> select min(no) "起始编号", 2 max(no) "终止编号", 3 count(1) "记录数", 4 status "记录状态" 5 from (select no, status, row_number() over(order by status, no) rn 6 from tt 7 order by no) 8 group by status, (rn - no); 起始编号 终止编号 记录数 记录状态 ---------- ---------- ---------- ---------- 1 3 3 1 4 5 2 2 6 8 3 1
------解决方案--------------------
SQL> select min(id) "起始编号",
2 max(id) "终止编号",
3 count(1) "记录数",
4 status "记录状态"
5 from (select id, status, row_number() over(partition by status order by id) rn
6 from rpt
7 order by id)
8 group by status, (rn - id);
起始编号 终止编号 记录数 记录状态
---------- ---------- ---------- ----------
1 3 3 1
4 5 2 2
6 8 3 1