SQL查询结果出现重复行，如何解决并按日期分组？

你的查询出现重复行的核心原因是连接条件和分组维度不匹配：

• 两个子查询都是按DATE(credit_date)/DATE(debit_date)分组，所以每个子查询中同一个月份会返回多条记录（对应不同日期）
• 你用cc.month = cd.month作为连接条件，这会让左边子查询里该月的每一条日期记录，和右边子查询里该月的每一条日期记录进行交叉连接（笛卡尔积），最终导致结果行数爆炸、数据重复

下面分两种常见需求给出修正方案：

需求1：按日期分别汇总当日的收支（每日一行） #

如果你的目标是每天一行，展示当天的总入账和总出账，那么应该按日期（而不是月份）进行连接，同时用LEFT JOIN+COALESCE确保即使某天只有收入或只有支出也能显示：

SELECT 
    COALESCE(cc.date, cd.date) AS record_date,
    MONTHNAME(COALESCE(cc.date, cd.date)) AS month,
    ROUND(IFNULL(cc.credit_amount, 0), 2) AS credit,
    ROUND(IFNULL(cd.debit_amount, 0), 2) AS debit
FROM (
    SELECT 
        DATE(credit_date) AS date,
        IFNULL(SUM(credit_amount), 0) AS credit_amount
    FROM cust_credit 
    WHERE YEAR(credit_date) = YEAR(NOW())
    GROUP BY DATE(credit_date)
) cc
FULL OUTER JOIN (
    SELECT 
        DATE(debit_date) AS date,
        IFNULL(SUM(debit_amount), 0) AS debit_amount
    FROM cust_debit 
    WHERE YEAR(debit_date) = YEAR(NOW())
    GROUP BY DATE(debit_date)
) cd ON cc.date = cd.date
ORDER BY record_date;

注：如果你的数据库不支持FULL OUTER JOIN（比如MySQL），可以用UNION ALL先收集所有日期，再左连接两个子查询：

WITH all_dates AS (
    SELECT DATE(credit_date) AS date FROM cust_credit WHERE YEAR(credit_date) = YEAR(NOW())
    UNION
    SELECT DATE(debit_date) AS date FROM cust_debit WHERE YEAR(debit_date) = YEAR(NOW())
)
SELECT 
    ad.date AS record_date,
    MONTHNAME(ad.date) AS month,
    ROUND(IFNULL(cc.credit_amount, 0), 2) AS credit,
    ROUND(IFNULL(cd.debit_amount, 0), 2) AS debit
FROM all_dates ad
LEFT JOIN (
    SELECT DATE(credit_date) AS date, IFNULL(SUM(credit_amount),0) AS credit_amount
    FROM cust_credit WHERE YEAR(credit_date)=YEAR(NOW()) GROUP BY DATE(credit_date)
) cc ON ad.date = cc.date
LEFT JOIN (
    SELECT DATE(debit_date) AS date, IFNULL(SUM(debit_amount),0) AS debit_amount
    FROM cust_debit WHERE YEAR(debit_date)=YEAR(NOW()) GROUP BY DATE(debit_date)
) cd ON ad.date = cd.date
ORDER BY ad.date;

需求2：按月汇总当月的总收支（每月一行） #

如果你的目标是每月一行，展示当月的总收入和总支出，那么两个子查询都应该按月份分组，而不是按日期：

SELECT 
    cc.month,
    ROUND(cc.credit_amount, 2) AS credit,
    ROUND(IFNULL(cd.debit_amount, 0), 2) AS debit
FROM (
    SELECT 
        MONTHNAME(credit_date) AS month,
        MONTH(credit_date) AS month_num, -- 用于排序，避免月份名称按字母排序
        IFNULL(SUM(credit_amount), 0) AS credit_amount
    FROM cust_credit 
    WHERE YEAR(credit_date) = YEAR(NOW())
    GROUP BY MONTHNAME(credit_date), MONTH(credit_date)
) cc
LEFT JOIN (
    SELECT 
        MONTHNAME(debit_date) AS month,
        MONTH(debit_date) AS month_num,
        IFNULL(SUM(debit_amount), 0) AS debit_amount
    FROM cust_debit 
    WHERE YEAR(debit_date) = YEAR(NOW())
    GROUP BY MONTHNAME(debit_date), MONTH(debit_date)
) cd ON cc.month_num = cd.month_num
ORDER BY cc.month_num;

你可以根据自己的实际需求选择对应的方案，核心就是保证分组维度和连接条件的一致性，避免不必要的交叉连接。

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