with temp as( select 1 id,1 ord,100 score from dual union all select 1 id,2 ord,80 score from dual union all select 1 id,3 ord,70 score from dual union all select 1 id,4 ord,100 score from dual union all select 1 id,5 ord,60 score from dual union all select 1 id,6 ord,50 score from dual union all select 1 id,7 ord,40 score from dual union all select 1 id,8 ord,100 score from dual union all select 1 id,9 ord,100 score from dual union all select 1 id,10 ord,100 score from dual ) select distinct id from( select id,ord,score,score2,lead(score2) over(partition by id order by ord) score3 from( select id,ord,score,lead(score) over(partition by id order by ord) score2 from temp ) ) where score - score2 > 0 and score2 - score3 > 0
with tb as( select 'aspen' sname,to_date('20100111','yyyymmdd')ttime,100 score from dual union all select 'aspen',to_date('20100211','yyyymmdd'),90 from dual union all select 'aspen',to_date('20100311','yyyymmdd'),80 from dual union all select 'aspen',to_date('20100411','yyyymmdd'),82 from dual union all select 'scott',to_date('20100111','yyyymmdd'),70 from dual union all select 'scott',to_date('20100211','yyyymmdd'),60 from dual union all select 'scott',to_date('20100311','yyyymmdd'),90 from dual ) select sname from( select sname, ttime, score, row_number() over(partition by sname order by ttime) rn from tb) where level>=3 --需要连续几次这个level就>=几 start with rn=1 connect by prior score>score and prior rn=rn-1
with tb as( select 'aspen' sname,to_date('20100111','yyyymmdd')ttime,100 score from dual union all select 'aspen',to_date('20100211','yyyymmdd'),90 from dual union all select 'aspen',to_date('20100311','yyyymmdd'),80 from dual union all select 'aspen',to_date('20100411','yyyymmdd'),82 from dual union all select 'aspen',to_date('20100511','yyyymmdd'),81 from dual union all select 'aspen',to_date('20100611','yyyymmdd'),79 from dual union all select 'aspen',to_date('20100711','yyyymmdd'),77 from dual union all select 'aspen',to_date('20100811','yyyymmdd'),70 from dual union all select 'scott',to_date('20100111','yyyymmdd'),70 from dual union all select 'scott',to_date('20100211','yyyymmdd'),60 from dual union all select 'scott',to_date('20100311','yyyymmdd'),90 from dual ) select sname from( select sname, ttime, score, row_number() over(partition by sname order by ttime) rn from tb) where level>=4 --需要连续几次这个level就>=几 start with rn=1 connect by prior score>score and prior rn=rn-1改成这个就不行了!
--修改了下 欢迎拍砖 with tb as( select 'aspen' sname,to_date('20100111','yyyymmdd')ttime,100 score from dual union all select 'aspen',to_date('20100211','yyyymmdd'),90 from dual union all select 'aspen',to_date('20100311','yyyymmdd'),80 from dual union all select 'aspen',to_date('20100411','yyyymmdd'),82 from dual union all select 'aspen',to_date('20100511','yyyymmdd'),81 from dual union all select 'aspen',to_date('20100611','yyyymmdd'),79 from dual union all select 'aspen',to_date('20100711','yyyymmdd'),77 from dual union all select 'aspen',to_date('20100811','yyyymmdd'),70 from dual union all select 'scott',to_date('20100111','yyyymmdd'),70 from dual union all select 'scott',to_date('20100211','yyyymmdd'),60 from dual union all select 'scott',to_date('20100311','yyyymmdd'),90 from dual ) select distinct sname from (select sname, ttime, score, row_number() over(partition by sname order by ttime) rn from tb) t where level>=4 start with rn=rn connect by prior score>score and prior rn=rn-1SNAME ----- aspen
with tb as (select 'a' sname, to_date('20100111', 'yyyymmdd') ttime, 100 score from dual union all select 'a', to_date('20100211', 'yyyymmdd'), 90 from dual union all select 'a', to_date('20100311', 'yyyymmdd'), 80 from dual union all select 'a', to_date('20100411', 'yyyymmdd'), 82 from dual union all select 'b', to_date('20100111', 'yyyymmdd'), 81 from dual union all select 'b', to_date('20100211', 'yyyymmdd'), 79 from dual union all select 'b', to_date('20100311', 'yyyymmdd'), 78 from dual union all select 'c', to_date('20100111', 'yyyymmdd'), 70 from dual union all select 'c', to_date('20100211', 'yyyymmdd'), 71 from dual union all select 'c', to_date('20100311', 'yyyymmdd'), 72 from dual union all select 'c', to_date('20100411', 'yyyymmdd'), 90 from dual) select distinct sname from (select sname, ttime, score, row_number() over(partition by sname order by ttime asc) rn from tb) t where level >= 3 start with rn = rn connect by prior score > score and prior sname = sname and prior rn = rn - 1
select 1 id,1 ord,100 score from dual
union all
select 1 id,2 ord,80 score from dual
union all
select 1 id,3 ord,70 score from dual
union all
select 1 id,4 ord,100 score from dual
union all
select 1 id,5 ord,60 score from dual
union all
select 1 id,6 ord,50 score from dual
union all
select 1 id,7 ord,40 score from dual
union all
select 1 id,8 ord,100 score from dual
union all
select 1 id,9 ord,100 score from dual
union all
select 1 id,10 ord,100 score from dual
)
select distinct id from(
select id,ord,score,score2,lead(score2) over(partition by id order by ord) score3 from(
select id,ord,score,lead(score) over(partition by id order by ord) score2 from temp
)
) where score - score2 > 0 and score2 - score3 > 0
with tb as(
select 'aspen' sname,to_date('20100111','yyyymmdd')ttime,100 score from dual
union all
select 'aspen',to_date('20100211','yyyymmdd'),90 from dual
union all
select 'aspen',to_date('20100311','yyyymmdd'),80 from dual
union all
select 'aspen',to_date('20100411','yyyymmdd'),82 from dual
union all
select 'scott',to_date('20100111','yyyymmdd'),70 from dual
union all
select 'scott',to_date('20100211','yyyymmdd'),60 from dual
union all
select 'scott',to_date('20100311','yyyymmdd'),90 from dual
)
select sname
from(
select sname,
ttime,
score,
row_number() over(partition by sname order by ttime) rn
from tb)
where level>=3 --需要连续几次这个level就>=几
start with rn=1
connect by prior score>score and prior rn=rn-1
with tb as(
select 'aspen' sname,to_date('20100111','yyyymmdd')ttime,100 score from dual
union all
select 'aspen',to_date('20100211','yyyymmdd'),90 from dual
union all
select 'aspen',to_date('20100311','yyyymmdd'),80 from dual
union all
select 'aspen',to_date('20100411','yyyymmdd'),82 from dual
union all
select 'aspen',to_date('20100511','yyyymmdd'),81 from dual
union all
select 'aspen',to_date('20100611','yyyymmdd'),79 from dual
union all
select 'aspen',to_date('20100711','yyyymmdd'),77 from dual
union all
select 'aspen',to_date('20100811','yyyymmdd'),70 from dual
union all
select 'scott',to_date('20100111','yyyymmdd'),70 from dual
union all
select 'scott',to_date('20100211','yyyymmdd'),60 from dual
union all
select 'scott',to_date('20100311','yyyymmdd'),90 from dual
)
select sname
from(
select sname,
ttime,
score,
row_number() over(partition by sname order by ttime) rn
from tb)
where level>=4 --需要连续几次这个level就>=几
start with rn=1
connect by prior score>score and prior rn=rn-1改成这个就不行了!
with tb as(
select 'aspen' sname,to_date('20100111','yyyymmdd')ttime,100 score from dual
union all
select 'aspen',to_date('20100211','yyyymmdd'),90 from dual
union all
select 'aspen',to_date('20100311','yyyymmdd'),80 from dual
union all
select 'aspen',to_date('20100411','yyyymmdd'),82 from dual
union all
select 'aspen',to_date('20100511','yyyymmdd'),81 from dual
union all
select 'aspen',to_date('20100611','yyyymmdd'),79 from dual
union all
select 'aspen',to_date('20100711','yyyymmdd'),77 from dual
union all
select 'aspen',to_date('20100811','yyyymmdd'),70 from dual
union all
select 'scott',to_date('20100111','yyyymmdd'),70 from dual
union all
select 'scott',to_date('20100211','yyyymmdd'),60 from dual
union all
select 'scott',to_date('20100311','yyyymmdd'),90 from dual
)
select distinct sname
from
(select sname,
ttime,
score,
row_number() over(partition by sname order by ttime) rn
from tb) t
where level>=4
start with rn=rn
connect by prior score>score and prior rn=rn-1SNAME
-----
aspen
(select 'a' sname, to_date('20100111', 'yyyymmdd') ttime, 100 score
from dual
union all
select 'a', to_date('20100211', 'yyyymmdd'), 90
from dual
union all
select 'a', to_date('20100311', 'yyyymmdd'), 80
from dual
union all
select 'a', to_date('20100411', 'yyyymmdd'), 82
from dual
union all
select 'b', to_date('20100111', 'yyyymmdd'), 81
from dual
union all
select 'b', to_date('20100211', 'yyyymmdd'), 79
from dual
union all
select 'b', to_date('20100311', 'yyyymmdd'), 78
from dual
union all
select 'c', to_date('20100111', 'yyyymmdd'), 70
from dual
union all
select 'c', to_date('20100211', 'yyyymmdd'), 71
from dual
union all
select 'c', to_date('20100311', 'yyyymmdd'), 72
from dual
union all
select 'c', to_date('20100411', 'yyyymmdd'), 90
from dual)
select distinct sname
from (select sname,
ttime,
score,
row_number() over(partition by sname order by ttime asc) rn
from tb) t
where level >= 3
start with rn = rn
connect by prior score > score
and prior sname = sname
and prior rn = rn - 1