查找连续的数据2

WITH t AS (
SELECT 1 idd,175093762 nu FROM dual UNION 
SELECT 1,175093763 FROM dual UNION 
SELECT 1,175093764 FROM dual UNION 
SELECT 1,175093765 FROM dual UNION 
SELECT 1,175093766 FROM dual UNION 
SELECT 1,175093767 FROM dual UNION
SELECT 1,175093800 FROM dual UNION 
SELECT 2,178281287 from dual UNION 
SELECT 2,178281288 FROM dual UNION 
SELECT 2,178281390 FROM dual UNION 
SELECT 2,178281391 FROM dual UNION 
SELECT 2,178281392 FROM dual 
)
SELECT * FROM t;


需求：得到结果
idd   nu1          nu2
1     175093762    175093767
1     175093800    175093800
2     178281287    178281288
2     178281390    178281392




WITH t AS (
SELECT 1 idd,175093762 nu FROM dual UNION 
SELECT 1,175093763 FROM dual UNION 
SELECT 1,175093764 FROM dual UNION 
SELECT 1,175093765 FROM dual UNION 
SELECT 1,175093766 FROM dual UNION 
SELECT 1,175093767 FROM dual UNION
SELECT 1,175093800 FROM dual UNION 
SELECT 2,178281287 from dual UNION 
SELECT 2,178281288 FROM dual UNION 
SELECT 2,178281390 FROM dual UNION 
SELECT 2,178281391 FROM dual UNION 
SELECT 2,17828139 FROM dual 
)
,Y AS(
SELECT IDD,NU, LAG(NU)OVER(PARTITION BY IDD ORDER BY NU) NU1 FROM T
)
,Z AS(
 SELECT IDD,NU,NU1,CASE WHEN (NU-NVL(NU1,NU))<>1 THEN 1 ELSE 0 END XX FROM Y 
)
SELECT IDD,NU,nu1, SUM(XX)OVER(ORDER BY NU) FROM Z ORDER BY idd,nu;




WITH t AS (
SELECT 1 idd,175093762 nu FROM dual UNION 
SELECT 1,175093763 FROM dual UNION 
SELECT 1,175093764 FROM dual UNION 
SELECT 1,175093765 FROM dual UNION 
SELECT 1,175093766 FROM dual UNION 
SELECT 1,175093767 FROM dual UNION
SELECT 1,175093800 FROM dual UNION 
SELECT 2,178281287 from dual UNION 
SELECT 2,178281288 FROM dual UNION 
SELECT 2,178281390 FROM dual UNION 
SELECT 2,178281391 FROM dual UNION 
SELECT 2,17828139 FROM dual 
)
,Y AS(
SELECT IDD,NU, LAG(NU)OVER(PARTITION BY IDD ORDER BY NU) NU1 FROM T
)
,Z AS(
 SELECT IDD,NU,NU1,CASE WHEN (NU-NVL(NU1,NU))<>1 THEN 1 ELSE 0 END XX FROM Y 
)
,xx AS (
SELECT IDD,NU,nu1, SUM(XX)OVER(PARTITION BY idd ORDER BY NU) AS group_id FROM Z ORDER BY idd,nu
)
SELECT idd,GROUP_id,MIN(nu) AS min_nu,MAX(nu) AS max_nu FROM xx GROUP BY idd,group_id;