MySQL中SUM函数使用求助：优化薪资扣除项汇总查询语句

我编写了一段包含SUM函数的MySQL联合查询代码，用于统计薪资扣除项，希望输出包含CustID、DeductionName（扣除项名称）、DeductionAmount（扣除金额）三列，按扣除项名称排序并汇总各类扣除项总金额的结果，烦请协助调整代码实现该目标。

原始查询代码 #

SELECT `test`.custinfo.CustID, OtherDeductionName AS DeductionName, sum(OtherDeductionAmount) as DeductionAmount FROM `db_payroll`.`tbl_otherdeductions`
LEFT JOIN `db_payroll`.tbl_payroll
ON `db_payroll`.tbl_payroll.OtherDeductionsID = `db_payroll`.tbl_otherdeductions.OtherDeductionsID
LEFT JOIN `test`.tbl_testpayinternal on tbl_testpayinternal.PRID = tbl_payroll.PRID
LEFT JOIN `test`.tbl_testpay on tbl_testpay.PRID = tbl_payroll.PRID
LEFT JOIN `test`.custinfo on(CASE WHEN tbl_payroll.EmpID LIKE '%EX%' THEN `test`.custinfo.custid = `test`.tbl_testpay.custid ELSE `test`.custinfo.custid = `test`.tbl_testpayinternal.custid END)
WHERE `tbl_payroll`.`status` = 'Done' and `test`.custinfo.CustID = '00000008' and `db_payroll`.`tbl_payroll`.date_start = '2022-11-14' and `db_payroll`.`tbl_payroll`.date_end = '2022-11-28' 
AND `db_payroll`.`tbl_payroll`.OtherDeductionsID <> ''
GROUP BY DeductionName
UNION
SELECT `test`.custinfo.CustID, StatutoryDeductionsName AS DeductionName, sum(StatutoryEE) as DeductionAmount FROM `test`.`tbl_statutorydeductions`
LEFT JOIN `test`.tbl_statutorydeductionstype
ON `test`.tbl_statutorydeductions.StatutoryDeductionsTypeID = `test`.tbl_statutorydeductionstype.StatutoryDeductionsTypeID
LEFT JOIN `db_payroll`.tbl_payroll
ON `db_payroll`.tbl_payroll.StatutoryDeductionsID = `test`.tbl_statutorydeductions.StatutoryDeductionsID
LEFT JOIN `test`.tbl_testpayinternal on tbl_testpayinternal.PRID = tbl_payroll.PRID
LEFT JOIN `test`.tbl_testpay on tbl_testpay.PRID = tbl_payroll.PRID
LEFT JOIN `test`.custinfo on(CASE WHEN tbl_payroll.EmpID LIKE '%EX%' THEN `test`.custinfo.custid = `test`.tbl_testpay.custid ELSE `test`.custinfo.custid = `test`.tbl_testpayinternal.custid END)
WHERE `tbl_payroll`.`status` = 'Done' and `test`.custinfo.CustID = '00000008' and `db_payroll`.`tbl_payroll`.date_start = '2022-11-14' and `db_payroll`.`tbl_payroll`.date_end = '2022-11-28'
AND `db_payroll`.`tbl_payroll`.StatutoryDeductionsID <> ''
GROUP BY DeductionName
UNION
SELECT `test`.custinfo.CustID, TypeOfLoan AS DeductionName, sum(AmortAmount) as DeductionAmount FROM `test`.`tbl_benamortloan`
LEFT JOIN `db_payroll`.tbl_payroll
ON `test`.tbl_benamortloan.IDno = `db_payroll`.tbl_payroll.EmpID AND `test`.tbl_benamortloan.CutOffID = `db_payroll`.tbl_payroll.CutOffID
LEFT JOIN `test`.tbl_testpayinternal on tbl_testpayinternal.PRID = tbl_payroll.PRID
LEFT JOIN `test`.tbl_testpay on tbl_testpay.PRID = tbl_payroll.PRID
LEFT JOIN `test`.custinfo on(CASE WHEN tbl_payroll.EmpID LIKE '%EX%' THEN `test`.custinfo.custid = `test`.tbl_testpay.custid ELSE `test`.custinfo.custid = `test`.tbl_testpayinternal.custid END)
WHERE  `tbl_payroll`.`status` = 'Done' and `test`.custinfo.CustID = '00000008' and `db_payroll`.`tbl_payroll`.date_start = '2022-11-14' and `db_payroll`.`tbl_payroll`.date_end = '2022-11-28'
AND `db_payroll`.`tbl_payroll`.LoanPaymentsID <> ''
GROUP BY DeductionName
ORDER BY DeductionName

调整后的查询代码 #

SELECT cust_id AS CustID, deduction_name AS DeductionName, SUM(deduction_amount) AS DeductionAmount
FROM (
    -- 其他扣除项统计
    SELECT 
        ci.CustID AS cust_id,
        od.OtherDeductionName AS deduction_name,
        SUM(od.OtherDeductionAmount) AS deduction_amount
    FROM `db_payroll`.`tbl_otherdeductions` od
    INNER JOIN `db_payroll`.tbl_payroll pr ON pr.OtherDeductionsID = od.OtherDeductionsID
    LEFT JOIN `test`.tbl_testpayinternal pi ON pi.PRID = pr.PRID
    LEFT JOIN `test`.tbl_testpay p ON p.PRID = pr.PRID
    INNER JOIN `test`.custinfo ci ON 
        (pr.EmpID LIKE '%EX%' AND ci.custid = p.custid) 
        OR (pr.EmpID NOT LIKE '%EX%' AND ci.custid = pi.custid)
    WHERE pr.`status` = 'Done' 
        AND ci.CustID = '00000008' 
        AND pr.date_start = '2022-11-14' 
        AND pr.date_end = '2022-11-28' 
        AND pr.OtherDeductionsID <> ''
    GROUP BY ci.CustID, od.OtherDeductionName

    UNION ALL

    -- 法定扣除项统计
    SELECT 
        ci.CustID AS cust_id,
        sdt.StatutoryDeductionsName AS deduction_name,
        SUM(sd.StatutoryEE) AS deduction_amount
    FROM `test`.`tbl_statutorydeductions` sd
    INNER JOIN `test`.tbl_statutorydeductionstype sdt ON sd.StatutoryDeductionsTypeID = sdt.StatutoryDeductionsTypeID
    INNER JOIN `db_payroll`.tbl_payroll pr ON pr.StatutoryDeductionsID = sd.StatutoryDeductionsID
    LEFT JOIN `test`.tbl_testpayinternal pi ON pi.PRID = pr.PRID
    LEFT JOIN `test`.tbl_testpay p ON p.PRID = pr.PRID
    INNER JOIN `test`.custinfo ci ON 
        (pr.EmpID LIKE '%EX%' AND ci.custid = p.custid) 
        OR (pr.EmpID NOT LIKE '%EX%' AND ci.custid = pi.custid)
    WHERE pr.`status` = 'Done' 
        AND ci.CustID = '00000008' 
        AND pr.date_start = '2022-11-14' 
        AND pr.date_end = '2022-11-28'
        AND pr.StatutoryDeductionsID <> ''
    GROUP BY ci.CustID, sdt.StatutoryDeductionsName

    UNION ALL

    -- 贷款扣除项统计
    SELECT 
        ci.CustID AS cust_id,
        bal.TypeOfLoan AS deduction_name,
        SUM(bal.AmortAmount) AS deduction_amount
    FROM `test`.`tbl_benamortloan` bal
    INNER JOIN `db_payroll`.tbl_payroll pr ON bal.IDno = pr.EmpID AND bal.CutOffID = pr.CutOffID
    LEFT JOIN `test`.tbl_testpayinternal pi ON pi.PRID = pr.PRID
    LEFT JOIN `test`.tbl_testpay p ON p.PRID = pr.PRID
    INNER JOIN `test`.custinfo ci ON 
        (pr.EmpID LIKE '%EX%' AND ci.custid = p.custid) 
        OR (pr.EmpID NOT LIKE '%EX%' AND ci.custid = pi.custid)
    WHERE pr.`status` = 'Done' 
        AND ci.CustID = '00000008' 
        AND pr.date_start = '2022-11-14' 
        AND pr.date_end = '2022-11-28'
        AND pr.LoanPaymentsID <> ''
    GROUP BY ci.CustID, bal.TypeOfLoan
) AS combined_deductions
GROUP BY cust_id, deduction_name
ORDER BY deduction_name;

关键调整说明 #

1. 修正GROUP BY子句：将CustID加入GROUP BY，符合MySQL严格模式下的分组要求，避免因ONLY_FULL_GROUP_BY导致的语法错误。
2. 优化关联条件写法：将CASE表达式改写为OR连接的条件判断，更符合JOIN条件的标准写法，提升查询可读性与执行效率。
3. 调整JOIN类型：将部分LEFT JOIN改为INNER JOIN，因为WHERE条件中过滤了custinfo.CustID，LEFT JOIN会自动转为INNER JOIN，显式声明更清晰。
4. 使用UNION ALL替代UNION：如果不需要对结果去重，UNION ALL的执行效率更高；若需去重则换回UNION即可。
5. 子查询封装统一汇总：将三个查询的结果封装到子查询中，再统一进行分组汇总，确保结果格式一致，避免因各子查询单独分组导致的异常。
6. 添加表别名：简化代码书写，提升可读性。

内容的提问来源于stack exchange，提问作者allan