ratio_to_report更新表

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/559237/viewspace-631348/