MySQL按日期分组统计全部状态与过期状态数据的方法

可以通过条件聚合函数实现需求，将总交易数与expired状态数量的统计逻辑整合到同一查询中，按日期分组返回结果：

SELECT 
    DATE_FORMAT(insert_date, '%d-%m-%Y') AS `date`,
    COUNT(*) AS total_transactions,
    SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END) AS expired_count
FROM transaction
WHERE insert_date BETWEEN '2022-11-24 00:00:00' AND '2022-12-08 23:59:59'
GROUP BY DATE_FORMAT(insert_date, '%d-%m-%Y')
ORDER BY `date`;

关键逻辑说明： #

• COUNT(*)：统计每个日期下的所有交易总数，不受status字段取值影响。
• SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END)：通过CASE表达式判断每条记录的status是否为expired，符合条件则返回1，否则返回0，最终用SUM累加得到该日期下expired状态的总数量。
• 分组条件改为按格式化后的完整日期（%d-%m-%Y）分组，确保每个日期仅返回一行结果。
• 追加ORDER BY可让结果按日期顺序排列，提升可读性。

如果使用的是MySQL这类支持DATE()函数的数据库，也可以用以下写法优化性能——分组时直接基于日期类型而非字符串，仅在输出时做格式转换：

SELECT 
    DATE_FORMAT(insert_date, '%d-%m-%Y') AS `date`,
    COUNT(*) AS total_transactions,
    SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END) AS expired_count
FROM transaction
WHERE insert_date BETWEEN '2022-11-24 00:00:00' AND '2022-12-08 23:59:59'
GROUP BY DATE(insert_date)
ORDER BY DATE(insert_date);

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