ORACLE单张表逐年比较某个字段是否增加打上标识

[lou3578]

SOS

[yaiger]

把lhq+ck+sfq的和做比较

[lou3578]

引用 1 楼 yaiger的回复:

把lhq+ck+sfq的和做比较

能不能写个例子 大佬 我还是个萌新 万分感谢 sum可以的 但是 不会写…

[nayi_224]

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

[lou3578]

出来了 万分感谢 感谢【2

[AHUA1001]

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 ;

[lou3578]

引用 5 楼 AHUA1001的回复:

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 ;

谢谢 我也试了试你这个 好像出不来 …

[大脸猫o]

抛砖引玉吧，我觉得这个提问的重点是位置偏移函数的使用，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

[AHUA1001]

引用 6 楼 qq_40175369 的回复:

[quote=引用 5 楼 AHUA1001的回复:]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 ;

谢谢 我也试了试你这个 好像出不来 …[/quote]
加索引name +nd

[nayi_224]

分析函数、层次查询、递归with、model、自连接、标量子查询、存储过程都可以实现，考虑到效率，后三个不予考虑，考虑到简易程度，分析函数是首选。