oracle合并連續數值

原始資料    

ID PID   

1000 1010

1011 1050

1051 1056

1059 1073

1200 1210

1211 1240

1501 1570

1571 1580

1581 1600      

效果

startNO endNo

1000 1056

1059 1073

1200 1240

1501 1600

select id, lead(priv,1,pid) over(order by id) - 1 pid
    from
    (
    WITH cat AS
    (
    select '1000' ID, '1010'+1 PID FROM dual union
    SELECT '1011' ID, '1050'+1 PID FROM dual UNION
    SELECT '1051' ID, '1056'+1 PID FROM dual UNION
    SELECT '1059' ID, '1072'+1 PID FROM dual UNION
    SELECT '1200' ID, '1210'+1 PID FROM dual UNION
    SELECT '1211' ID, '1240'+1 PID FROM dual UNION
    SELECT '1501' ID, '1570'+1 PID FROM dual UNION
    SELECT '1571' ID, '1580'+1 PID FROM dual UNION
    SELECT '1581' ID, '1600'+1 PID FROM dual
    )
    SELECT ID, pid, (id - lag(pid,1,1)over(order by id)) diff,  lag(pid,1,1)over(order by id) priv
    FROM cat  
    )  where diff<>0;
           

其實上面的語句仍然有部分問題，最後的兩條diff=0的資料在整合的時候被過濾掉了，如何解決？ 1、造一條比max(id),max(pid)還大的id值，然後再進行查詢； 2、利用數值連續的作為分組取最大最小值 SQL代碼如下：

select groupid, min(id), max(pid)
from 
(
select id, pid,  lead(priv,1,pid) over(order by id), sum(diff) over(order by id) groupid
from
(
with tmp as(
select 1000 as id,  1010 as pid  from dual union all
select 1011 as id,  1050 as pid  from dual union all
select 1051 as id,  1056 as pid  from dual union all
select 1059 as id,  1073 as pid  from dual union all
select 1200 as id,  1210 as pid  from dual union all
select 1211 as id,  1240 as pid  from dual union all
select 1501 as id,  1570 as pid  from dual union all
select 1571 as id,  1580 as pid  from dual union all
select 1581 as id,  1600 as pid  from dual  
)
select id , pid,  lag(pid,1,1) over(order by pid) priv,
       id - lag(pid, 1, 1) over(order by id) -1 diff 
from tmp
) a
)
group by groupid;
           

或者是

select  groupid, min(id), max(pid)
from
(
select id, pid, sum(low) over(order by id) as groupid 
from 
(
with tmp as(
select 1000 as id,  1010 as pid  from dual union all
select 1011 as id,  1050 as pid  from dual union all
select 1051 as id,  1056 as pid  from dual union all
select 1059 as id,  1073 as pid  from dual union all
select 1200 as id,  1210 as pid  from dual union all
select 1211 as id,  1240 as pid  from dual union all
select 1501 as id,  1570 as pid  from dual union all
select 1571 as id,  1580 as pid  from dual union all
select 1581 as id,  1600 as pid  from dual  
)
select id , pid,  
       MAX(pid) over(order by id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) max_pid,
       (case when MAX(pid+1) over(order by id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= id then 0 else 1 end) as Low
from tmp
) 
) group by groupid;
           

實際業務中，可能還會出現交集、子集等的情況，以下為解決方案：

with tmp as(  

select 1005 as id,  1010 as pid  from dual union all  

select 1003 as id,  1020 as pid  from dual union all 

select 1000 as id,  1050 as pid  from dual union all  

select 1051 as id,  1056 as pid  from dual union all  

select 1059 as id,  1073 as pid  from dual union all  

select 1200 as id,  1210 as pid  from dual union all  

select 1211 as id,  1240 as pid  from dual union all  

select 1501 as id,  1570 as pid  from dual union all  

select 1571 as id,  1580 as pid  from dual union all  

select 1581 as id,  1600 as pid  from dual    

), tmp1 as

(

select id ,  pid,  

       (case when  max(pid) over(order by id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= id+1          

       then max(pid) over(order by id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) else pid end) new_pid

from tmp  

), tmp2 as

(

select id, pid, new_pid,  

        (case when lag(new_pid,1,1) over(order by id)+1  >= id then 0 else 1 end) diff

from tmp1

), tmp3 as

(

select id, pid,  new_pid, diff, sum(diff) over(order by id) groupid  

from tmp2

)

select groupid, min(id), max(new_pid)  

From tmp3

group by groupid