oracle sql语句实现累加、累减、累乘、累除

在oracle开发过程中经常会遇到累加、减、乘、除的问题。下面对这个做个小的总结

---创建测试表

CREATE TABLE TEST(

    PARENT_ID NUMBER,

    PART_ID NUMBER,

    QUALITY NUMBER);

INSERT INTO TEST VALUES(1,1,2);

INSERT INTO TEST VALUES(1,2,3);

INSERT INTO TEST VALUES(1,3,2);

INSERT INTO TEST VALUES(1,4,5);

INSERT INTO TEST VALUES(2,2,3);

INSERT INTO TEST VALUES(2,3,5);

INSERT INTO TEST VALUES(2,4,7);

一、实现累加

SELECT

    T.*,

    SUM(T.QUALITY) OVER (PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RUNNING_QUALITY

FROM

    TEST T

二、实现累减

SELECT

    T.PARENT_ID,

    T.PART_ID,

    T.QUALITY,

    SUM(DECODE(T.RN,1,T.QUALITY,-T.QUALITY)) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RUNNING_PROD

FROM

    (

        SELECT

            T.*,

            ROW_NUMBER() OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RN

        FROM

            TEST T) T

三、实现累乘

SELECT

    T.*,

    ROUND(EXP(SUM(LN(T.QUALITY)) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID)),0) RUNNING_PROD

FROM

    TEST T

四、实现累除

SELECT

    T.PARENT_ID,

    T.PART_ID,

    T.QUALITY,

    EXP(SUM(DECODE(RN,1,LN(T.QUALITY),-LN(T.QUALITY))) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID)) RUNNING_PROD

FROM

    (

        SELECT

            T.*,

            ROW_NUMBER() OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RN

        FROM

            TEST T)T

累乘和累除的缺陷是不能对负数进行运算因为ln(负数)没有意义

巴特西

oracle sql语句实现累加、累减、累乘、累除

最新文章

热门文章