PHP中列求和结果异常及期初余额SQL问题求助

Hey there, let's break down why your queries aren't behaving as expected and fix them up step by step!

Problem 1: Incorrect Sum Result #

Your current sum query returns 500 instead of the expected 600, and the root cause is duplicate rows created by your LEFT JOIN. Here's the breakdown:

• Your CR_table has 2 rows with components_key = '202012162458' (total credit: 400 + 300 = 700)
• Your DR_table has 1 matching row (debit: 100)
• When you join the tables, each CR row pairs with the single DR row, so the DR debit value gets counted twice (100 * 2 = 200)
• That leads to 700 - 200 = 500, which doesn't match your expected result.

Correct SQL for Sum Calculation #

Instead of joining first and then summing, aggregate each table separately first, then join the results. This eliminates duplicate row issues entirely:

SELECT 
    COALESCE(cr_total.total_credit, 0) - COALESCE(dr_total.total_debit, 0) AS final_balance
FROM 
    (
        SELECT SUM(credit) AS total_credit 
        FROM CR_table 
        WHERE components_key = '202012162458'
    ) AS cr_total
LEFT JOIN 
    (
        SELECT SUM(req_debit) AS total_debit 
        FROM DR_table 
        WHERE transaction_type = 'OA' 
          AND components_key = '202012162458'
    ) AS dr_total
ON 1=1; -- Join works here since both subqueries return a single aggregated row

• COALESCE handles cases where one table has no matching rows (it replaces NULL with 0 to avoid breaking the subtraction)
• This will correctly calculate 700 - 100 = 600 as you expected.

Problem 2: Broken Opening Balance Query #

Your opening balance query has syntax errors (mismatched parentheses, missing logical joins) and unclear aggregation logic. Assuming your goal is to calculate the opening balance as:

Total valid credits from CR_table (before 2020-12-29) minus total valid debits from DR_table (before 2020-12-29, transaction_type='OA') for the target components_key.

Correct SQL for Opening Balance #

SELECT SUM(AMT) AS OpeningBalance
FROM (
    -- Get total valid credits from CR_table
    SELECT SUM(credit) AS AMT
    FROM CR_table
    WHERE components_key = '202012162458'
      AND insert_date < '2020-12-29'
    
    UNION ALL
    
    -- Get negative total valid debits (so SUM effectively subtracts them)
    SELECT -SUM(req_debit) AS AMT
    FROM DR_table
    WHERE transaction_type = 'OA'
      AND components_key = '202012162458'
      AND insert_date < '2020-12-29'
) AS t;

• UNION ALL combines the two aggregated results (credit total and negative debit total)
• Summing these values gives you the net opening balance without syntax errors.

If your original logic intended to pair individual CR and DR rows instead of aggregating first, feel free to clarify, but this structure fixes the core issues and delivers the correct aggregated balance.

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