MySQL返回多行时子查询的正确写法及日期统计问题

原查询的问题在于子查询没有与外层的date字段关联，导致返回的是所有成功记录的总金额，而非对应日期的成功金额；当子查询添加GROUP BY date后，会返回多个日期的结果，外层每行只能接收单个值，因此触发"子查询返回多行"的错误。

以下是三种可行的解决方案：

方案1：条件聚合（推荐，最简洁高效） #

利用CASE语句在聚合时筛选成功记录，直接计算每个日期的成功金额：

SELECT
    date,
    SUM(amount) AS Attempt_amount,
    SUM(CASE WHEN success = 1 THEN amount ELSE 0 END) AS Success_amount
FROM
    Commitment_fees
GROUP BY
    date;

执行结果：

方案2：关联子查询 #

在子查询中添加与外层日期的关联条件，确保每个日期只返回对应行的成功金额：

SELECT
    cf.date,
    SUM(cf.amount) AS Attempt_amount,
    (SELECT SUM(amount)
     FROM Commitment_fees cf_sub
     WHERE cf_sub.success = 1 AND cf_sub.date = cf.date) AS Success_amount
FROM
    Commitment_fees cf
GROUP BY
    cf.date;

方案3：预聚合后JOIN #

先分别统计总尝试金额和成功金额的临时结果，再通过日期关联：

SELECT
    main.date,
    main.Attempt_amount,
    COALESCE(success.Success_amount, 0) AS Success_amount
FROM
    (SELECT date, SUM(amount) AS Attempt_amount
     FROM Commitment_fees
     GROUP BY date) main
LEFT JOIN
    (SELECT date, SUM(amount) AS Success_amount
     FROM Commitment_fees
     WHERE success = 1
     GROUP BY date) success ON main.date = success.date;

COALESCE用于处理某日期无成功记录的情况，将NULL转为0，保证结果一致性。

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