测试表tt ID 1 2 3 4 5 6 7 8 9 11 12 17 19select id from ( select id,lead(id) over (order by id) as next_id,lag(id,1) over (order by id) as pre_id from tt ) where id=next_id-1 or id=pre_id+1 结果 1 2 3 4 5 6 7 8 9 11 12
SQL> select * from (select id, lead(id) over(order by id) r1, id+1 r2, lag(id) o ver(order by id) r3, id-1 r4 from test_lead) where r1=r2 or r3=r4;
SQL> select * from (select id, lead(id) over(order by id) r1, id+1 r2, lag(id) o ver(order by id) r3, id-1 r4 from test_lead) where r1=r2 or r3=r4;
测试表tt ID 1 2 3 4 5 6 7 8 9 11 12 17 19 select id from ( select id,lead(id) over (order by id) as next_id,lag(id,1) over (order by id) as pre_id from tt ) where id=next_id-1 or id=pre_id+1 结果 1 2 3 4 5 6 7 8 9 11 12
ID
1
2
3
4
5
6
7
8
9
11
12
17
19select id from
(
select id,lead(id) over (order by id) as next_id,lag(id,1) over (order by id) as pre_id from tt
) where id=next_id-1 or id=pre_id+1
结果
1
2
3
4
5
6
7
8
9
11
12
ver(order by id) r3, id-1 r4 from test_lead) where r1=r2 or r3=r4;
ver(order by id) r3, id-1 r4 from test_lead) where r1=r2 or r3=r4;
ID
1
2
3
4
5
6
7
8
9
11
12
17
19 select id from
(
select id,lead(id) over (order by id) as next_id,lag(id,1) over (order by id) as pre_id from tt
) where id=next_id-1 or id=pre_id+1
结果
1
2
3
4
5
6
7
8
9
11
12