MySQL 生成累计差

末蓝、 2022-12-18 07:56 115阅读 0赞

备注:测试数据库版本为MySQL 8.0

# 一.需求 #

如果表中既有库存，又有出货记录，根据表type字段来判断，想想求出截止当前出货时间的当前库存

测试数据:

drop table t;
    
    create table t(id int,type varchar(100),amt decimal(15,2));
    
    insert into t values (1,'库存',100000.00);
    insert into t values (2,'出货',2000.00);
    insert into t values (3,'出货',8000.00);
    insert into t values (4,'出货',10000.00);
    insert into t values (5,'出货',15000.00);

# 二.解决方案 #

其实和累计和差不多，只是将库存沿用整数，出货用负数来表示即可

-- 普通写法
    SELECT t.id,t.type,t.amt 
           ,case when t.type = '库存' then t.amt else -t.amt end as new_amt
           ,( select sum( case when t2.type = '库存' then t2.amt else -t2.amt end ) from t t2 where t2.id <= t.id) lj_amt
    from t
    order by id
    
    -- 分析函数写法
    SELECT t.id,t.type,t.amt 
           ,case when t.type = '库存' then t.amt else -t.amt end as new_amt
           ,sum(case when t.type = '库存' then t.amt else -t.amt end) over (order by id) lj_amt
    from t
    order by id

**测试记录**

mysql> SELECT t.id,t.type,t.amt
        ->        ,case when t.type = '库存' then t.amt else -t.amt end as new_amt
        ->        ,( select sum( case when t2.type = '库存' then t2.amt else -t2.amt end ) from t t2 where t2.id <= t.id) lj_amt
        -> from t
        -> order by id;
    +------+--------+-----------+-----------+-----------+
    | id   | type   | amt       | new_amt   | lj_amt    |
    +------+--------+-----------+-----------+-----------+
    |    1 | 库存   | 100000.00 | 100000.00 | 100000.00 |
    |    2 | 出货   |   2000.00 |  -2000.00 |  98000.00 |
    |    3 | 出货   |   8000.00 |  -8000.00 |  90000.00 |
    |    4 | 出货   |  10000.00 | -10000.00 |  80000.00 |
    |    5 | 出货   |  15000.00 | -15000.00 |  65000.00 |
    +------+--------+-----------+-----------+-----------+
    5 rows in set (0.00 sec)
    
    mysql> SELECT t.id,t.type,t.amt
        ->        ,case when t.type = '库存' then t.amt else -t.amt end as new_amt
        ->        ,sum(case when t.type = '库存' then t.amt else -t.amt end) over (order by id) lj_amt
        -> from t
        -> order by id;
    +------+--------+-----------+-----------+-----------+
    | id   | type   | amt       | new_amt   | lj_amt    |
    +------+--------+-----------+-----------+-----------+
    |    1 | 库存   | 100000.00 | 100000.00 | 100000.00 |
    |    2 | 出货   |   2000.00 |  -2000.00 |  98000.00 |
    |    3 | 出货   |   8000.00 |  -8000.00 |  90000.00 |
    |    4 | 出货   |  10000.00 | -10000.00 |  80000.00 |
    |    5 | 出货   |  15000.00 | -15000.00 |  65000.00 |
    +------+--------+-----------+-----------+-----------+
    5 rows in set (0.00 sec)