表T中有两个字段:类别和序号;类别为a,b, 序号为0001,0002,0003,0005,0006等可能不连续的字符串
求查询出结果如下的sql语句:
类别 开始序号 终止序号 数量
a 0001 0003 3
a 0005 0006 2
b 0001 0012 12
...
数据库为oracle
求查询出结果如下的sql语句:
类别 开始序号 终止序号 数量
a 0001 0003 3
a 0005 0006 2
b 0001 0012 12
...
数据库为oracle
调试欢乐多
(select type,xuhao,to_number(xuhao)-row_number()over(partition by type order by xuhao as rn from t ) group by type,rn;
思路,没测试过!
表中两个字段,一个char(1) 一个char(4);
with tb as(
select 'a' id,'0001' seq from dual union all
select 'a','0002' from dual union all
select 'a','0003' from dual union all
select 'a','0005' from dual union all
select 'a','0006' from dual union all
select 'b','0001' from dual union all
select 'b','0002' from dual union all
select 'b','0003' from dual)
--以上为提供数据的语句
select id,min(seq),max(seq),count(*)
from tb
group by id,seq-rownum
order by id
I MIN( MAX( COUNT(*)
- ---- ---- ----------
a 0001 0003 3
a 0005 0006 2
b 0001 0003 3