MySQL按用户ID和自然日分组聚合账单数据的实现方法

当前查询未实现用户+自然日维度聚合，核心问题有两点：

• 分组维度不对：date字段是精度到秒的timestamp类型，直接用原始date字段分组时，哪怕两条记录属于同一天，只要时分秒有差异就会被判定为不同分组。你SELECT中格式化的date带了时分秒精度，同样无法作为日维度分组的依据。
• 非分组字段未做聚合：SELECT列表中的id、精确时间值、total_amount都没有配套聚合逻辑，分组后数据库会随机返回组内单条记录的对应值，不仅达不到聚合效果，在开启ONLY_FULL_GROUP_BY的MySQL环境中还会直接抛出语法错误。

按用户+自然日聚合的正确写法如下，示例逻辑为返回每个用户当日最新一笔记录的基础信息，同时统计当日总欠款金额：

$st = $conn->prepare("
    SELECT
        MAX(id) AS record_id,
        DATE_FORMAT(MAX(date), '%d/%m/%y %H:%i') AS record_date,
        name,
        id_user,
        SUM(total_amount) AS day_total
    FROM CAJA
    WHERE active != 0
    GROUP BY id_user, DATE(date), name
    ORDER BY record_id DESC
");
$st->execute();
while($row = $st->fetch(PDO::FETCH_ASSOC)) {
    // 自行编写业务处理逻辑
}

• 分组时用DATE(date)把timestamp类型的字段截断到日级精度，确保同一天的记录会被分到同一组
• 因为同一个用户当日可能有多条记录，用MAX(id)、MAX(date)取当日最新生成的收据id和时间，如果你需要取当日最早的记录，把MAX()替换为MIN()即可
• 用SUM(total_amount)统计用户当日的总欠款金额，如果你的业务不需要求和，要取最大值、平均值等其他统计结果，替换为对应聚合函数即可
• 分组字段加上name是为了适配不同SQL_MODE的语法校验，避免因用户姓名字段不在分组列表中报错

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