MySQL实现累加、累乘、累减、累除

最新推荐文章于 2023-09-04 17:06:17 发布

铁血阿张

最新推荐文章于 2023-09-04 17:06:17 发布

阅读量4.5k

点赞数 2

分类专栏： SQL

本文链接：https://blog.csdn.net/weixin_43618989/article/details/104953316

版权

SQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

CREATE TABLE TEST(
    PARENT_ID INTEGER,
    PART_ID INTEGER,
    QUALITY INTEGER);

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);

在这里插入图片描述

1.累加

SELECT
    T.*,
    SUM(T.QUALITY) OVER (PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RUNNING_QUALITY
FROM
    TEST T;

在这里插入图片描述

2.累乘

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;

在这里插入图片描述

3.累减

SELECT
    T.PARENT_ID,
    T.PART_ID,
    T.QUALITY,
    SUM(if(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;

在这里插入图片描述

4.累除

SELECT
    T.PARENT_ID,
    T.PART_ID,
    T.QUALITY,
    EXP(SUM(if(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;