17,086
社区成员
发帖
与我相关
我的任务
分享
SQL> select * from t;
A B
--------------------------------------- --------------------
1 #1杆塔
2 #2杆塔
3 #3.1杆塔
4 #3.2杆塔
5 #5杆塔
6 #4杆塔
7 #6杆塔
8 #7杆塔
9 #9杆塔
10 #8杆塔
10 rows selected
SQL>
SQL> with tt as (
2 select a, b,c-lag(c,1,0)over(order by a) d,lag(c)over(order by a) e from (
3 select a,b,replace(replace(b,'#',''),'杆塔','') c from t
4 )
5 )
6 select a,b from tt where tt.a in(
7 select a from tt where d<0
8 union
9 select cast(e as number) from tt where d<0
10 );
A B
--------------------------------------- --------------------
5 #5杆塔
6 #4杆塔
9 #9杆塔
10 #8杆塔
SQL>
SQL> select * from t;
A B
--------------------------------------- --------------------
1 #1¸ËËþ
2 #2¸ËËþ
3 #3.1¸ËËþ
4 #3.2¸ËËþ
5 #5¸ËËþ
6 #4¸ËËþ
7 #6¸ËËþ
8 #7¸ËËþ
9 #9¸ËËþ
10 #8¸ËËþ
10 rows selected
SQL>
SQL> with tt as (
2 select a, b,c-lag(c,1,0)over(order by a) d,lag(c)over(order by a) e from (
3 select a,b,replace(replace(b,'#',''),'¸ËËþ','') c from t
4 )
5 )
6 select a,b from tt where tt.a in(
7 select a from tt where d<0
8 union
9 select cast(e as number) from tt where d<0
10 );
A B
--------------------------------------- --------------------
5 #5¸ËËþ
6 #4¸ËËþ
9 #9¸ËËþ
10 #8¸ËËþ
SQL>
with temp
as
(
select 1 b,'#1杆塔'a from dual
union all
select 2,'#2杆塔' from dual
union all
select 3,'#3.1杆塔' from dual
union all
select 4,'#3.2杆塔' from dual
union all
select 5,'#5杆塔' from dual
union all
select 6,'#4杆塔' from dual
union all
select 7,'#6杆塔' from dual
union all
select 8,'#7杆塔' from dual
union all
select 9,'#9杆塔' from dual
union all
select 10,'#8杆塔' from dual
)
select a,b
from
(select a,b,row_number() over(partition by 1 order by a) rn from temp order by a,b)
where b<>rn
--result:
#4杆塔 6
#5杆塔 5
#8杆塔 10
#9杆塔 9