自己解决了,分享下解决方法,解决方法如下:select a.*,dense_rank() over(order by rownumber - rownums)
from (
select a.*,rownum as rownums from (
select a.line_name,a.model_name,a.virtual_id,max(a.in_station_time) maxtime,min(a.in_station_time) mintime,count(*) as I_qty,row_number()over(order by line_name,min(a.in_station_time) asc) as rownumber from (
select a.line_name,b.model_name,c.virtual_id,a.in_station_time from sfism4.r_wip_log_t a left join sfism4.r_wip_tracking_t b on a.serial_number=b.serial_number
left join sfism4.r_sn_mapping_group_t c on b.serial_number=c.serial_number
where (a.emp_no <>'IN_STOCK' and a.emp_no<>'OUT_STOCK' or a.emp_no is null) and a.group_name ='SMTC'
and a.in_station_time>=to_date('2019/4/26 11:22:37','yyyy-mm-dd hh24:mi:ss')
and a.in_station_time<=to_date('2019/4/26 17:22:36','yyyy-mm-dd hh24:mi:ss')
and c.virtual_id is not null
order by a.line_name,a.in_station_time) a
group by a.line_name,a.model_name,a.virtual_id
order by a.line_name)a where a.I_Qty>1 order by line_name,mintime
) a
以上是SQL语句,最关键的代码在于先根据row_number()over(order by line_name,min(a.in_station_time) asc)来增加一个自增的数字列,然后在sql外面套一层select查询出rownum,再套一个select增加一个列dense_rank() over(order by rownumber - rownums) ,就可以区分出来每个连续的区间。