时间戳

首页 > 解决方案 > 创建平衡列

mysql - 创建平衡列

问题描述

我有三个表， t_PRODS (ID, NAME, OLD_QTY)和 t_INS (ID, PROVIDER_NAME, PRODUCT_ID, QTY)， t_OUTS (ID, DEPLOYER_NAME, PRODUCT_ID, QTE)然后我用 UNION ALL 将它们全部加入以显示它们，两列用于 QTY，第一列用于 QTY_IN，另一列用于 QTE_OUT

SELECT 'Old quantity' AS OPERATION, ID, OLD_QTY AS Q_IN, NULL AS Q_OUT FROM t_PRODS
UNION ALL
SELECT PROVIDER_NAME, PRODUCT_ID, QTY, NULL FROM t_INS
UNION ALL
SELECT DEPLOYER_NAME, PRODUCT_ID, NULL, QTY FROM t_OUTS

我的问题是我想显示另一列这样的余额：

OPERATION        ID    Q_IN     Q_OUT     **BALANCE**
Old quantity     1     2        NULL      2
PROVIDER_1       1     20       NULL      22
PROVIDER_2       3     6        NULL      28
DEPLOYER_1       1     NULL     3         25
DEPLOYER_1       1     NULL     6         19
PROVIDER_2       4     5        NULL      24
PROVIDER_3       2     6        NULL      30
DEPLOYER_2       2     NULL     12        18
DEPLOYER_3       4     NULL     7         11

真的我无法解决这个问题，任何帮助将不胜感激！

标签： mysql

解决方案

SELECT 'Old quantity' AS OPERATION, ID, OLD_QTY AS Q_IN, NULL AS Q_OUT, BALACNE = 'BALANCE (of previous record) + Q_IN (of current record) - Q_OUT (of current record)' FROM t_PRODS
UNION ALL
SELECT PROVIDER_NAME, PRODUCT_ID, QTY, NULL, NULL FROM t_INS
UNION ALL
SELECT DEPLOYER_NAME, PRODUCT_ID, NULL,NULL, QTY FROM t_OUTS

注意：确保替换'BALANCE (of previous record) + Q_IN (of current record) - Q_OUT (of current record)'为从中获取值以计算余额的列

推荐阅读