MySQL：按两列相同值汇总列及按日期币种统计收支求助

Hey Eric, let's break down how to solve this problem—you're trying to summarize transaction data so you can see total income and expenses grouped by date and currency, and your initial GROUP BY + OUTER JOIN approach didn't work out. Let's walk through the simplest (and most efficient) solutions first, then address why your JOIN method might have failed.

First, Let's Assume Your Raw Table Structure #

I'll start with a common transaction table setup (adjust if your schema is different):

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    trans_date DATE NOT NULL,
    currency VARCHAR(3) NOT NULL, -- e.g., USD, EUR, GBP
    amount DECIMAL(10,2) NOT NULL,
    trans_type ENUM('income', 'expense') NOT NULL -- marks if it's incoming or outgoing
);

Solution 1: Conditional Aggregation (Simplest & Fastest) #

Instead of messing with joins, use CASE WHEN inside SUM() to calculate income and expenses in a single pass over the data. This is far more efficient than splitting into subqueries and joining.

SELECT
    trans_date AS `date`,
    currency,
    -- Sum only income amounts, default to 0 if no income for the date/currency
    SUM(CASE WHEN trans_type = 'income' THEN amount ELSE 0 END) AS total_income,
    -- Sum only expense amounts, default to 0 if no expenses for the date/currency
    SUM(CASE WHEN trans_type = 'expense' THEN amount ELSE 0 END) AS total_expense
FROM transactions
GROUP BY trans_date, currency -- Group by the two columns you care about
ORDER BY trans_date, currency;

How This Works: #

• The CASE statements filter which amounts get included in each sum.
• GROUP BY trans_date, currency ensures each row represents a unique date + currency combination.
• If there's no income (or no expenses) for a given group, the sum defaults to 0 instead of NULL, making the output cleaner.

Solution 2: Fixing Your GROUP BY + JOIN Approach #

If you specifically want to use joins (maybe for more complex logic later), the issue was likely not handling NULL values correctly or missing a full outer join (MySQL has quirks with this). Here's how to do it properly:

For MySQL 8.0+ (Supports FULL OUTER JOIN) #

-- First, create separate summaries for income and expenses
WITH income_summary AS (
    SELECT trans_date, currency, SUM(amount) AS total_income
    FROM transactions
    WHERE trans_type = 'income'
    GROUP BY trans_date, currency
),
expense_summary AS (
    SELECT trans_date, currency, SUM(amount) AS total_expense
    FROM transactions
    WHERE trans_type = 'expense'
    GROUP BY trans_date, currency
)
-- Join the two summaries, using COALESCE to handle missing groups
SELECT
    COALESCE(i.trans_date, e.trans_date) AS `date`,
    COALESCE(i.currency, e.currency) AS currency,
    COALESCE(i.total_income, 0) AS total_income,
    COALESCE(e.total_expense, 0) AS total_expense
FROM income_summary i
FULL OUTER JOIN expense_summary e
    ON i.trans_date = e.trans_date AND i.currency = e.currency
ORDER BY `date`, currency;

For MySQL 5.x (No FULL OUTER JOIN Support) #

Use UNION ALL to combine the two summaries first, then re-group:

SELECT
    trans_date AS `date`,
    currency,
    SUM(total_income) AS total_income,
    SUM(total_expense) AS total_expense
FROM (
    -- Get all income groups
    SELECT trans_date, currency, SUM(amount) AS total_income, 0 AS total_expense
    FROM transactions
    WHERE trans_type = 'income'
    GROUP BY trans_date, currency
    UNION ALL
    -- Get all expense groups
    SELECT trans_date, currency, 0 AS total_income, SUM(amount) AS total_expense
    FROM transactions
    WHERE trans_type = 'expense'
    GROUP BY trans_date, currency
) AS combined_transactions
GROUP BY trans_date, currency
ORDER BY trans_date, currency;

Why Your Initial JOIN Failed: #

• You probably used a LEFT JOIN or RIGHT JOIN instead of a full outer join, which would miss groups where there's only income or only expenses.
• You didn't use COALESCE to replace NULL values with 0, leading to missing totals in your output.

Final Notes #

• The conditional aggregation method is almost always better here—it uses fewer resources and is easier to maintain.
• If your table has other filters (e.g., a date range), add a WHERE clause before the GROUP BY to narrow down the data first.

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