case when lhp > lag(lhp) over(partition by name order by nd) or lhp > lag(ck) over(partition by name order by nd) or lhp > lag(sfq) over(partition by name order by nd) then '增加' else '' end
SELECT t.*, (SELECT DECODE(COUNT(0),0,'未增加','增加') FROM table_name tt WHERE tt.name = t.name AND tt.nd - 1 = t.nd AND (tt.lhq < t.lhq OR tt.ck < t.ck OR tt.sfq < t.sfq)) z FROM table_name t ORDER BY t.name,t.nd ;
抛砖引玉吧,我觉得这个提问的重点是位置偏移函数的使用,lag,lead 还有一个比较复杂的我也没记住 好几个关键字的 with t as (select '小明' name, 0 lhq, 1 ck, 0 sfq, 2015 nd from dual union all select '小明' name, 0 lhq, 1 ck, 0 sfq, 2016 nd from dual union all select '小明' name, 1 lhq, 1 ck, 2 sfq, 2017 nd from dual union all select '小明' name, 1 lhq, 2 ck, 1 sfq, 2018 nd from dual union all select '小明' name, 3 lhq, 0 ck, 0 sfq, 2019 nd from dual) select m.*, case when lhq > lhq or ck > ck_next or sfq > sfq_next then '增加' else '未增加' end as flag from (select name, lhq, lag(lhq) over(order by nd) lhq_next, ck, lag(ck) over(order by nd) ck_next, sfq, lag(sfq) over(order by nd) sfq_next, nd from t) m
lhp > lag(ck) over(partition by name order by nd) or
lhp > lag(sfq) over(partition by name order by nd)
then '增加'
else '' end
(SELECT DECODE(COUNT(0),0,'未增加','增加') FROM table_name tt WHERE tt.name = t.name AND tt.nd - 1 = t.nd AND (tt.lhq < t.lhq OR tt.ck < t.ck OR tt.sfq < t.sfq)) z
FROM table_name t ORDER BY t.name,t.nd ;
with t as
(select '小明' name, 0 lhq, 1 ck, 0 sfq, 2015 nd
from dual
union all
select '小明' name, 0 lhq, 1 ck, 0 sfq, 2016 nd
from dual
union all
select '小明' name, 1 lhq, 1 ck, 2 sfq, 2017 nd
from dual
union all
select '小明' name, 1 lhq, 2 ck, 1 sfq, 2018 nd
from dual
union all
select '小明' name, 3 lhq, 0 ck, 0 sfq, 2019 nd
from dual)
select m.*,
case
when lhq > lhq or ck > ck_next or sfq > sfq_next then
'增加'
else
'未增加'
end as flag
from (select name,
lhq,
lag(lhq) over(order by nd) lhq_next,
ck,
lag(ck) over(order by nd) ck_next,
sfq,
lag(sfq) over(order by nd) sfq_next,
nd
from t) m
加索引name +nd