ORACLE单张表批量处理不一样的数据

[lou3578]

求大佬帮忙

[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 ;
加索引name +nd

[wildwolv]

思路： 1、先找出distinct name和zjnd； 2、计算出每个name和zjnd前三年和后三年的sum（ck）； 3、对sum（ck）进行case when判断，如果大于10000，则标记富有 代码如下： select d.name,d.ck,d.ssnd,d.zjnd, case when c.zck > 10000 then '富有' else null end from table d, ( select b.name,a.zjnd,sum(b.ck) as zck from table b, (selecct distinct name,zjnd from table) a where a.name = b.name and b.zjnd >= a.zjnd - 3 and b.zjnd <= a.zjnd + 2 group by b.name,a.zjnd ) c where d.name = c.name and d.zjnd = c.zjnd

[lou3578]

出来了 谢谢各位大佬

[Mricoo_周]

大致给你个思路你看可以么（暂定你的表名为tab1）
1、定义个游标,按name和zjnd聚合
cursor cur1 is select name,zjnd from tab1 group by name,zjnd;
cur1_row cur1%rowtype;
2、开始循环跑逻辑
FOR cur1_row in cur1 loop
  SELECT sum(ck) into zck
    from tab1 t0
   where t0.zjnd = cur1_row.zjnd
     and t0.name = cur1_row.name
     and t0.ssnd >= cur1_row.zjnd - 3
     and t0.ssnd < cur1_row.zjnd + 3
  if zck > 10000 then
    update tab1 t1 
       set 更新的字段 = '富有'
     WHERE t1.zjnd = cur1_row.zjnd 
       and t1.name = cur1_row.name
       and t1.ssnd >= cur1_row.zjnd - 3
       and t1.ssnd < cur1_row.zjnd + 3;
    commit;
  end if;
end loop

[nayi_224]

别没事总想着存储过程，效率低bug还多，还不好改

with tab1 as (
select 1 id, 1 yet, 3 tar, 1000 num from dual union all
select 1 id, 2 yet, 3 tar, 1000 from dual union all
select 1 id, 3 yet, 3 tar, 3000 from dual union all
select 1 id, 4 yet, 3 tar, 3000 from dual union all
select 1 id, 5 yet, 3 tar, 3000 from dual union all
select 1 id, 6 yet, 3 tar, 3000 from dual 
)
, tab2 as (
select t1.*,
       sum(t1.num) over(partition by id order by yet range between 2 preceding and 2 following) so
  from tab1 t1
) 
select * from tab2 t1
 where t1.yet = t1.tar
;

ps：最好把建表语句都给出来