資源描述:
《oraclesql語句實現(xiàn)累加、累減、累乘、累除》由會員上傳分享,免費在線閱讀,更多相關內容在工程資料-天天文庫。
1、oraclesql語句實現(xiàn)累加、累減、累乘、累除收藏在oracle開發(fā)過程中經(jīng)常會遇到累加、減、乘、除的問題。下面對這個做個小的總結--創(chuàng)建測試表createtableaspen(parent_idnumber,part_idnumber,qntynumber);insertintoaspenvalues(1,1,2);insertintoaspenvalues(1,2,3);insertintoaspenvalues(1,3,2);insertintoaspenvalues(1,4,5);insertintoaspenvalu
2、es(2,2,3);insertintoaspenvalues(2,3,5);insertintoaspenvalues(2,4,7);--實現(xiàn)累加selectparent_id,part_id,qnty,sum(qnty)over(partitionbyparent_idorderbypart_id)running_prodfromaspenPARENT_IDPART_IDQNTYRUNNING_PROD------------------------------------------11221235132714512223
3、3235824715--實現(xiàn)累減selectparent_id,part_id,qnty,sum(decode(rn,1,qnty,-qnty))over(partitionbyparent_idorderbypart_id)running_prodfrom(selectparent_id,part_id,qnty,row_number()over(partitionbyparent_idorderbypart_id)rnfromaspen)PARENT_IDPART_IDQNTYRUNNING_PROD------------
4、------------------------------1122123-1132-3145-82233235-2247-9--實現(xiàn)累乘selectparent_id,part_id,exp(sum(ln(qnty))over(partitionbyparent_idorderbypart_id))running_prodfromaspenPARENT_IDPART_IDRUNNING_PROD-------------------------------11212613121460223231524105--實現(xiàn)累除sele
5、ctparent_id,part_id,exp(sum(decode(rn,1,ln(qnty),-ln(qnty)))over(partitionbyparent_idorderbypart_id))running_prodfrom(selectparent_id,part_id,qnty,row_number()over(partitionbyparent_idorderbypart_id)rnfromaspen)PARENT_IDPART_IDRUNNING_PROD----------------------------
6、----11212.66666666713.33333333314.06666666722323.624.085714286累乘和累除的缺陷是不能對負數(shù)進行運算因為ln(負數(shù))沒有意義本文來自CSDN博客,轉載請標明出處:http://blog.csdn.net/zhuomingwang/archive/2011/02/19/6194556.aspx