如何使用GROUP BY子句获取CR表中指定acc_id的最大start_time对应记录

嘿，我来帮你搞定这个SQL查询的问题～首先咱们先分析下你原来的查询为啥不对，然后给出几种靠谱的修正方法。

原查询的问题 #

你的原查询返回所有记录主要有两个核心问题：

1. 未关联acc_id与对应最大时间：子查询按acc_id分组取了每个组的MAX(start_time)，但外层用IN只是匹配所有包含这些时间值的行，没有把每行的acc_id和子查询中对应组的acc_id绑定——如果不同acc_id刚好有相同的最大时间，就会错误地把多个acc_id的行都拉出来。
2. 字符串时间的比较误差：你的start_time是字符串格式，直接用MAX(start_time)是按字符顺序排序，这可能得到错误的时间最大值（比如"12.30 am"作为字符串会比"1.30 am"大，但实际时间1.30 am比12.30 am晚）。
3. 另外你原查询的WHERE ACC_ID in (100,200)和你需要查询的200、300不符，这也是一个小细节问题。

修正方案 #

方案1：使用窗口函数（推荐，现代SQL写法） #

窗口函数ROW_NUMBER()可以给每个acc_id分组内的记录按时间倒序编号，然后取编号为1的记录（也就是时间最新的那条）：

SELECT acc_id, cr_id
FROM (
    SELECT 
        acc_id, 
        cr_id,
        -- 把字符串时间转成时间类型，再按倒序编号
        ROW_NUMBER() OVER (
            PARTITION BY acc_id 
            ORDER BY STR_TO_DATE(start_time, '%h.%i %p') DESC
        ) AS rn
    FROM CR
    WHERE acc_id IN (200, 300) -- 指定要查询的acc_id
) t
WHERE rn = 1; -- 取每个分组的第一条（时间最新）

这里STR_TO_DATE(start_time, '%h.%i %p')是把字符串格式的时间转成可比较的时间类型，确保排序的准确性。

方案2：使用关联子查询 #

通过关联子查询，给每个行匹配对应acc_id的最大时间（转成时间类型后），只保留匹配的行：

SELECT c.acc_id, c.cr_id
FROM CR c
WHERE c.acc_id IN (200, 300)
AND STR_TO_DATE(c.start_time, '%h.%i %p') = (
    -- 对子查询中的acc_id和外层的acc_id做关联，确保取的是当前acc_id的最大时间
    SELECT MAX(STR_TO_DATE(start_time, '%h.%i %p'))
    FROM CR
    WHERE acc_id = c.acc_id
);

额外建议 #

最好把start_time字段的类型改成TIME或DATETIME，这样就不用每次查询都做字符串转时间的操作，既提升性能又避免格式转换可能带来的错误。

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