我在计算银行对账单的列时遇到了一个问题。实际上，我正在用mysql php计算期初余额和期末余额。我有两张table。所用材料 IN + 项目和材料审批 OUT - . 在我的代码中一切都很完美，但我无法计算期初和期末余额。我正在尝试显示上一个期末余额，下一列将显示为期初余额。第一次，期初余额将默认为 0 .
我是php新手，我在这里使用union all函数。请帮助我在代码中创建获取开/关计算的函数。
我用了 CASE WHEN 函数，但我无法将上一列数据实现为下一行列。
请帮助以mu代码实现所需的输出。 table- material_in idcomponents\u keyinsert\u datecredittransaction\u type1123452021-04-16100英寸123452021-04-1650英寸 table- material_approve idcomponents\u keyinsert\u datedebittransaction\u type1123452021-04-1630out2123452021-04-162out
我在试密码-

SELECT
    `u`.`component_key`,
    `u`.`insert_date`,
    `u`.`transaction_qty`,
    `u`.`transaction_type`,
    CASE WHEN `u`.`query_type` = 'IN' THEN `u`.`transaction_qty` ELSE '0' END AS `today_credit`,
    CASE WHEN `u`.`query_type` = 'OUT' THEN `u`.`transaction_qty` ELSE '0' END AS `today_debit`,    
    CASE WHEN `u`.`query_type` = 'IN' OR `u`.`query_type` = 'OUT' THEN (`close_bal`) ELSE '0' END AS `open_bal`,
    CASE WHEN `u`.`query_type` = 'IN' OR `u`.`query_type` = 'OUT' THEN (`open_bal` + `today_credit`-`today_debit`) ELSE '0' END AS `close_bal`,
FROM
    (
    SELECT
        `components_key` AS `component_key`,
        `insert_date`,
        `credit` AS `transaction_qty`,
        CASE WHEN `transaction_type` = 'OA' THEN 'IN' WHEN `transaction_type` = 'O' THEN 'IN' WHEN `transaction_type` = 'I' THEN 'IN' ELSE '--'
        END AS `query_type`, null AS today_debit, null AS today_credit, null AS open_bal, null AS close_bal
    FROM
        `material_in`
    UNION ALL
    SELECT
        `components_key`,
        `insert_date`,
        `debit` AS `transaction_qty`,
        CASE WHEN `transaction_type` = 'OA' THEN 'OUT' WHEN `transaction_type` = 'I' THEN 'OUT' WHEN `transaction_type` = 'O' THEN 'OUT' ELSE '--'
        END AS `query_type`, null AS today_debit, null AS today_credit, null AS open_bal, null AS close_bal
    FROM
        `material_approve`
    ) u
WHERE
    `u`.`component_key` = '12345'
ORDER BY
    `u`.`insert_date`
DESC

输出来了-
组件\u密钥插入\u日期事务\u数量事务\u类型今天\u贷记今天\u借项打开\u余额关闭\u余额123452021-04-16100in1000null123452021-04-1650in500null123452021-04-1630out030null123452021-04-162out02nullnull
但我希望它是这样的，低于输出- output will be- 组件\u密钥插入\u日期交易\u数量交易\u类型今天\u贷记今天\u借项打开\u余额关闭\u余额123452021-04-16100in10000001123452021-04-1650in500150200123452021-04-1630out030200170123452021-04-162out02170168
闭式余额公式= open_bal + today_credit - today_debit = close_bal 未结余额公式= close_bal + today_credit - today_debit = open_bal