我帮你把GRP_ID求出来了,你可以直接对GRP_ID进行group就行了,剩下的你自己完善一下吧:with t as ( select 1 rec_id,0 distance from dual union all select 2 rec_id,0 distance from dual union all select 3 rec_id,0 distance from dual union all select 4 rec_id,1 distance from dual union all select 5 rec_id,2 distance from dual union all select 6 rec_id,3 distance from dual union all select 7 rec_id,4 distance from dual union all select 8 rec_id,0 distance from dual union all select 9 rec_id,1 distance from dual union all select 10 rec_id,2 distance from dual union all select 11 rec_id,3 distance from dual union all select 12 rec_id,4 distance from dual union all select 13 rec_id,5 distance from dual union all select 14 rec_id,6 distance from dual union all select 15 rec_id,0 distance from dual union all select 16 rec_id,1 distance from dual union all select 17 rec_id,6 distance from dual union all select 18 rec_id,8 distance from dual union all select 19 rec_id,11 distance from dual union all select 20 rec_id,13 distance from dual )select t1.rec_id,distance, decode(t1.distance, 0, rn, (select max(t2.rn) from (select t.*, rownum rn from t) t2 where t2.distance = 0 and t1.rn > t2.rn)) grp_id from (select t.*, rownum rn from t) t1;
或者:with t as ( select 1 rec_id,0 distance from dual union all select 2 rec_id,0 distance from dual union all select 3 rec_id,0 distance from dual union all select 4 rec_id,1 distance from dual union all select 5 rec_id,2 distance from dual union all select 6 rec_id,3 distance from dual union all select 7 rec_id,4 distance from dual union all select 8 rec_id,0 distance from dual union all select 9 rec_id,1 distance from dual union all select 10 rec_id,2 distance from dual union all select 11 rec_id,3 distance from dual union all select 12 rec_id,4 distance from dual union all select 13 rec_id,5 distance from dual union all select 14 rec_id,6 distance from dual union all select 15 rec_id,0 distance from dual union all select 16 rec_id,1 distance from dual union all select 17 rec_id,6 distance from dual union all select 18 rec_id,8 distance from dual union all select 19 rec_id,11 distance from dual union all select 20 rec_id,13 distance from dual ) select t3.rec_id, t3.distance, dense_rank() over(order by t3.rn) grp_id from (select t1.rec_id, distance, decode(t1.distance, 0, rn, (select max(t2.rn) from (select t.*, rownum rn from t) t2 where t2.distance = 0 and t1.rn > t2.rn)) rn from (select t.*, rownum rn from t) t1) t3;
我帮你把GRP_ID求出来了,你可以直接对GRP_ID进行group就行了,剩下的你自己完善一下吧:with t as
(
select 1 rec_id,0 distance from dual union all
select 2 rec_id,0 distance from dual union all
select 3 rec_id,0 distance from dual union all
select 4 rec_id,1 distance from dual union all
select 5 rec_id,2 distance from dual union all
select 6 rec_id,3 distance from dual union all
select 7 rec_id,4 distance from dual union all
select 8 rec_id,0 distance from dual union all
select 9 rec_id,1 distance from dual union all
select 10 rec_id,2 distance from dual union all
select 11 rec_id,3 distance from dual union all
select 12 rec_id,4 distance from dual union all
select 13 rec_id,5 distance from dual union all
select 14 rec_id,6 distance from dual union all
select 15 rec_id,0 distance from dual union all
select 16 rec_id,1 distance from dual union all
select 17 rec_id,6 distance from dual union all
select 18 rec_id,8 distance from dual union all
select 19 rec_id,11 distance from dual union all
select 20 rec_id,13 distance from dual
)select t1.rec_id,distance,
decode(t1.distance,
0,
rn,
(select max(t2.rn)
from (select t.*, rownum rn from t) t2
where t2.distance = 0
and t1.rn > t2.rn)) grp_id
from (select t.*, rownum rn from t) t1;
(
select 1 rec_id,0 distance from dual union all
select 2 rec_id,0 distance from dual union all
select 3 rec_id,0 distance from dual union all
select 4 rec_id,1 distance from dual union all
select 5 rec_id,2 distance from dual union all
select 6 rec_id,3 distance from dual union all
select 7 rec_id,4 distance from dual union all
select 8 rec_id,0 distance from dual union all
select 9 rec_id,1 distance from dual union all
select 10 rec_id,2 distance from dual union all
select 11 rec_id,3 distance from dual union all
select 12 rec_id,4 distance from dual union all
select 13 rec_id,5 distance from dual union all
select 14 rec_id,6 distance from dual union all
select 15 rec_id,0 distance from dual union all
select 16 rec_id,1 distance from dual union all
select 17 rec_id,6 distance from dual union all
select 18 rec_id,8 distance from dual union all
select 19 rec_id,11 distance from dual union all
select 20 rec_id,13 distance from dual
)
select t3.rec_id, t3.distance, dense_rank() over(order by t3.rn) grp_id
from (select t1.rec_id,
distance,
decode(t1.distance,
0,
rn,
(select max(t2.rn)
from (select t.*, rownum rn from t) t2
where t2.distance = 0
and t1.rn > t2.rn)) rn
from (select t.*, rownum rn from t) t1) t3;