在TB_LINE_INFLEXION 表中
line_id station_order
1 1
1 2
1 3
1 4
1 5
1 7
2 1
2 2
2 3
2 4
3 2
3 3
3 4
3 5
4 1
4 2
4 3
4 4
4 4
4 5
4 6line保存的是线路,station_order保存的是站点,站点应该是Station_Orde是一组从1开始的连续正整数,现在想查出有问题的线路。正确的应该是:同一线路,Station_Orde是一组从1开始的连续正整数得到的结果应该是:
line_id station_order
1 5
1 7
3 2
4 4
4 4就是如果线路中少了哪个,就把相邻的两条查出来,有重复的就把重复的查出来
line_id station_order
1 1
1 2
1 3
1 4
1 5
1 7
2 1
2 2
2 3
2 4
3 2
3 3
3 4
3 5
4 1
4 2
4 3
4 4
4 4
4 5
4 6line保存的是线路,station_order保存的是站点,站点应该是Station_Orde是一组从1开始的连续正整数,现在想查出有问题的线路。正确的应该是:同一线路,Station_Orde是一组从1开始的连续正整数得到的结果应该是:
line_id station_order
1 5
1 7
3 2
4 4
4 4就是如果线路中少了哪个,就把相邻的两条查出来,有重复的就把重复的查出来
from (
select line_id,station_order,
lag(station_order)over(partition by line_id order by station_order) s1,
lead(station_order)over(partition by line_id order by station_order) s2
from tb_line_inflexion )
where STATION_ORDER-s1<>1 or s2-STATION_ORDER<>1
;
from TB_LINE_INFLEXION t1
where exists (select 1 from TB_LINE_INFLEXION t2 where t2.line_id = t1.line_id and t2.station_order = t1.station_order and t2.rowid <> t1.rowid)
or not exists (select 1 from TB_LINE_INFLEXION t3 where (t3.line_id = t1.line_id and t3.station_order = t1.station_order - 1) or t1.station_order = 1)
or not exists (select 1 from TB_LINE_INFLEXION t3 where (t3.line_id = t1.line_id and t3.station_order = t1.station_order + 1) or t1.station_order = (select max(t4.station_order) from TB_LINE_INFLEXION t4 where t4.line_id = t1.line_id));
from TB_LINE_INFLEXION a
where not exists (select 1 from TB_LINE_INFLEXION where line_id=a.line_id and station_order=a.station_order+1)
or not exists (select 1 from TB_LINE_INFLEXION where line_id=a.line_id and station_order=a.station_order-1)
and station_order !=1
and station_order != (select max(station_order) from TB_LINE_INFLEXION where line_id=a.line_id )
在3楼兄弟的基础上改的:select t1.line_id,t1.station_order
from (
select line_id,station_order,
lag(station_order)over(partition by line_id order by station_order) s1,
lead(station_order)over(partition by line_id order by station_order) s2
from tb_line_inflexion ) t1
where (t1.STATION_ORDER-nvl(t1.s1, 0) <>1) or (t1.s2-t1.STATION_ORDER <>1)
;
select a.*,lag(station_order)over(partition by line_id order by station_order)lg,
lead(station_order)over(partition by line_id order by station_order)ld,
count(1)over(partition by line_id,station_order)c from TB_LINE_INFLEXION a)
where nvl(lg,0)<>station_order-1 or ld<>station_order+1 or c>1