ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ratio_to_report更新表

ratio_to_report更新表

原创 Linux操作系统 作者:prt 时间:2010-04-05 14:43:28 0 删除 编辑
ratio_to_report能查出来本级度量占上级度量的百分比,但如何更新表中的数据呢,用以下办法得出的都是错误的值(100),
update (select a.rowid ard,
               a.code1,
               a.code2,
               a.code3,
               a.c1,
               a.c2 ac2,
               b.rowid,
               round(100 * ratio_to_report(b.c1) over(partition by b.code2),2) ret
          from ta a, ta b
         where a.rowid = b.rowid)
   set ac2 = ret;
但用这个办法却可以正确更新数据:
merge into ta a
using (select b.code1,
              b.code2,
              b.code3,
              round(100 * ratio_to_report(c1) over(partition by code2), 2) res
         from ta b) b
on (b.code1 = a.code1
       and b.code2 = a.code2
       and b.code3 = a.code3)
when matched then
  update set a.c2 = b.res
也可以只更改ta表中的部分数据,如下sql:
merge into ta a
using (select b.code1,
              b.code2,
              b.code3,
              round(100 * ratio_to_report(c1) over(partition by code2), 2) res
         from ta b) b
on (b.code1 = a.code1
      and b.code2 = a.code2
     and b.code3 = a.code3
     and a.code1 in (1,2)
     )
when matched then
  update set a.c2 = b.res

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/559237/viewspace-631348/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-09

  • 博文量
    61
  • 访问量
    181779