with tb as( select 0 rq, 80 cnt from dual union all select 1, 100 from dual union all select 2, 200 from dual union all select 3, -100 from dual union all select 4, -5 from dual union all select 5, -5 from dual union all select 6, -50 from dual union all select 7, 90 from dual union all select 8, 800 from dual union all select 9, -80 from dual) select decode(sign(cnt),-1,'亏损',1,'盈利',null),max(lv) from (select rq,cnt,level lv from tb connect by prior rq=rq-1 and prior sign(cnt)=sign(cnt)) group by sign(cnt) DECO MAX(LV) ---- ---------- 盈利 3 亏损 4
你只要下面的语句(前面的是提供语句的语句) select decode(sign(cnt),-1,'亏损',1,'盈利',null),max(lv) from (select rq,cnt,level lv from tb connect by prior rq=rq-1 and prior sign(cnt)=sign(cnt)) group by sign(cnt)
--方法还是类似的啊 --觉得你这个需求有点不好理解,那你按照什么来排序呢? --假设按照rownum select decode(sign(cnt),-1,'亏损',1,'盈利',null),max(lv) from (select cnt,level lv from (select rownum rn,cnt from tb) connect by prior rn=rn-1 and prior sign(cnt)=sign(cnt)) group by sign(cnt)
with tb as(
select 0 rq, 80 cnt from dual union all
select 1, 100 from dual union all
select 2, 200 from dual union all
select 3, -100 from dual union all
select 4, -5 from dual union all
select 5, -5 from dual union all
select 6, -50 from dual union all
select 7, 90 from dual union all
select 8, 800 from dual union all
select 9, -80 from dual)
select decode(sign(cnt),-1,'亏损',1,'盈利',null),max(lv)
from (select rq,cnt,level lv
from tb
connect by prior rq=rq-1 and prior sign(cnt)=sign(cnt))
group by sign(cnt)
DECO MAX(LV)
---- ----------
盈利 3
亏损 4
假设这个表(假设名字为T2)很大很大的情况呢?
我想处理的表可能会有100000行左右……
有没有不输入每行数据的方法
select decode(sign(cnt),-1,'亏损',1,'盈利',null),max(lv)
from (select rq,cnt,level lv
from tb
connect by prior rq=rq-1 and prior sign(cnt)=sign(cnt))
group by sign(cnt)
我是新手
还是不太明白……比如我只关心我的表(t2)中的某一列(当日盈利)当日盈利
80
100
200
-100
-5
-5
-50
90
800
-80我想输出:最长连续盈利的天数(3)和最长连续亏损的天数(4)
应该如何写?
--方法还是类似的啊
--觉得你这个需求有点不好理解,那你按照什么来排序呢?
--假设按照rownum
select decode(sign(cnt),-1,'亏损',1,'盈利',null),max(lv)
from (select cnt,level lv
from (select rownum rn,cnt from tb)
connect by prior rn=rn-1 and prior sign(cnt)=sign(cnt))
group by sign(cnt)