如何用单条SQL查询同时统计每日竞赛报名与获奖金额？

方法一：条件聚合（高效推荐） #

直接通过CASE WHEN在聚合函数里区分交易类型，一次扫描表就能得到结果，是最优方案：

SELECT
    DATE(created_date) AS date,
    SUM(CASE WHEN transaction_mode = 'Joined the Contest' THEN amount ELSE 0 END) AS entry_amount,
    SUM(CASE WHEN transaction_mode = 'For Winning the Contest' THEN amount ELSE 0 END) AS credited_amount
FROM transaction
GROUP BY DATE(created_date)
ORDER BY date;

说明： #

• DATE(created_date)：提取日期部分（如果你的created_date是带时间戳的字段，不同数据库可能用CAST(created_date AS DATE)或TRUNC(created_date)，根据数据库调整）
• 两个SUM(CASE...)分别统计当日报名总金额和获奖总金额，没有对应交易的日期会自动填充0，不会出现NULL
• 最后按日期排序，保证结果有序

方法二：子查询关联（适合复杂场景） #

如果需要先分别统计再合并，可以用日期关联的方式，避免UNION ALL的列不匹配问题：

WITH entry_stats AS (
    SELECT
        DATE(created_date) AS date,
        SUM(amount) AS entry_amount
    FROM transaction
    WHERE transaction_mode = 'Joined the Contest'
    GROUP BY DATE(created_date)
),
credit_stats AS (
    SELECT
        DATE(created_date) AS date,
        SUM(amount) AS credited_amount
    FROM transaction
    WHERE transaction_mode = 'For Winning the Contest'
    GROUP BY DATE(created_date)
)
SELECT
    COALESCE(e.date, c.date) AS date,
    COALESCE(e.entry_amount, 0) AS entry_amount,
    COALESCE(c.credited_amount, 0) AS credited_amount
FROM entry_stats e
FULL OUTER JOIN credit_stats c ON e.date = c.date
ORDER BY date;

说明： #

• 用CTE先分别统计两种交易的每日金额
• FULL OUTER JOIN确保所有有报名或获奖记录的日期都被保留（如果数据库不支持FULL OUTER JOIN，比如MySQL，可以用LEFT JOIN加UNION来实现）
• COALESCE把NULL值替换为0，保证结果列没有空值

为什么你用UNION ALL会失败？ #

你之前的UNION ALL尝试只返回entry_amount，大概率是因为两个子查询的列结构不一致：比如第一个查询只输出date, entry_amount，第二个只输出date, credited_amount，UNION ALL会以第一个查询的列名为准，第二个查询的credited_amount会被当成entry_amount，而且日期会重复出现两次（一次对应报名，一次对应获奖），完全达不到合并的效果。所以这种场景下绝对不能用UNION ALL来合并，必须用上面两种方法。

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