一、demo脚本
UPDATE ld_bankaccountset a
LEFT JOIN (
SELECT
a.bankaccountsetid,
SUM(a.Total) AS total
FROM ld_cashbill a
WHERE a.deleted=0
AND a.bankaccountsetid>0
AND a.approvestatus<>'已拒绝'
AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=83 AND deploy_value=1) THEN a.approvestatus<>'已批准' ELSE 1=1 END)
GROUP BY a.bankaccountsetid
) sk ON sk.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
SELECT
a.inaccount,
SUM(a.cashmoney) AS total
FROM ld_cashdeposit a
WHERE a.deleted=0
AND a.inaccount>0
AND a.approvestatus<>'已拒绝'
AND a.approvestatus='已批准'
GROUP BY a.inaccount
) cx_in ON cx_in.inaccount=a.Bankaccountsetid
LEFT JOIN (
SELECT
a.inaccount,
SUM(a.cashmoney) AS total
FROM ld_putcash a
WHERE a.deleted=0
AND a.inaccount>0
AND a.approvestatus<>'已拒绝'
AND a.approvestatus='已批准'
GROUP BY a.inaccount
) tx_in ON tx_in.inaccount=a.Bankaccountsetid
LEFT JOIN (
SELECT
a.bankaccountsetid,
SUM(a.total) AS total
FROM ld_purchasecashback a
WHERE a.deleted=0
AND a.bankaccountsetid>0
-- AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=83 AND deploy_value=1) THEN a.approvestatus<>'已批准' ELSE 1=1 END)
GROUP BY a.bankaccountsetid
) tk_cg ON tk_cg.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
SELECT
a.bankaccountsetid,
SUM(a.Total) AS total
FROM ld_sendcashbill a
WHERE a.deleted=0
AND a.bankaccountsetid>0
AND a.approvestatus<>'已拒绝'
AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=83 AND deploy_value=1) THEN a.approvestatus<>'已批准' ELSE 1=1 END)
GROUP BY a.bankaccountsetid
) fk ON fk.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
SELECT
a.Outaccount,
SUM(a.cashmoney) AS total
FROM ld_cashdeposit a
WHERE a.deleted=0
AND a.inaccount>0
AND a.approvestatus<>'已拒绝'
AND a.approvestatus='已批准'
GROUP BY a.Outaccount
) cx_out ON cx_out.Outaccount=a.Bankaccountsetid
LEFT JOIN (
SELECT
a.Outaccount,
SUM(a.cashmoney) AS total
FROM ld_putcash a
WHERE a.deleted=0
AND a.Outaccount>0
AND a.approvestatus<>'已拒绝'
AND a.approvestatus='已批准'
GROUP BY a.Outaccount
) tx_out ON tx_out.Outaccount=a.Bankaccountsetid
LEFT JOIN (
SELECT
a.bankaccountsetid,
SUM(a.total) AS total
FROM ld_cashback a
WHERE a.deleted=0
AND a.bankaccountsetid>0
-- AND (CASE WHEN EXISTS (SELECT 1 FROM ld_systems_deploy WHERE deploy_type=83 AND deploy_value=1) THEN a.approvestatus<>'已批准' ELSE 1=1 END)
GROUP BY a.bankaccountsetid
) tk_xs ON tk_xs.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
SELECT
a.bankaccountsetid,
SUM(a.Total) AS total
FROM ld_expense a
WHERE a.deleted=0
AND a.bankaccountsetid>0
AND a.approvestatus<>'已拒绝'
AND a.approvestatus='已批准'
GROUP BY a.bankaccountsetid
) fy ON fy.bankaccountsetid=a.Bankaccountsetid
LEFT JOIN (
SELECT
a.bankaccountsetid,
SUM(a.Total) AS total
FROM ld_commission a
WHERE a.deleted=0
AND a.bankaccountsetid>0
AND a.approvestatus<>'已拒绝'
AND a.approvestatus='已批准'
GROUP BY a.bankaccountsetid
) yj ON yj.bankaccountsetid=a.Bankaccountsetid
SET
a.curbalancemoney=IFNULL(a.InitMoney,0)+IFNULL(sk.total,0)+IFNULL(cx_in.total,0)+IFNULL(tx_in.total,0)+IFNULL(tk_cg.total,0)-IFNULL(fk.total,0)-IFNULL(cx_out.total,0)-IFNULL(tx_out.total,0)-IFNULL(tk_xs.total,0)-IFNULL(fy.total,0)-IFNULL(yj.total,0)
WHERE IFNULL(a.curbalancemoney,0)<>IFNULL(a.InitMoney,0)+IFNULL(sk.total,0)+IFNULL(cx_in.total,0)+IFNULL(tx_in.total,0)+IFNULL(tk_cg.total,0)-IFNULL(fk.total,0)-IFNULL(cx_out.total,0)-IFNULL(tx_out.total,0)-IFNULL(tk_xs.total,0)-IFNULL(fy.total,0)-IFNULL(yj.total,0);
重算现金银行流水账.sql
