Laravel：groupBy后如何正确获取对应行的关联name字段

核心思路是先通过窗口函数标记每个房源的最新变更记录，再筛选出符合条件的记录并关联里程碑名称，最后完成统计。假设milestone_logs表存在记录创建时间的字段created_at（如果你的时间字段名称不同，替换成实际字段即可），具体SQL如下：

SELECT
    m.name AS from_milestone_name,
    COUNT(DISTINCT ml.listing_id) AS lost_count
FROM (
    SELECT
        listing_id,
        previous_milestone_id,
        created_at,
        -- 按房源分组，按创建时间倒序标记，最新记录标记为1
        ROW_NUMBER() OVER (PARTITION BY listing_id ORDER BY created_at DESC) AS rn
    FROM milestone_logs
    WHERE
        -- 筛选过去一年的记录
        created_at >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
        -- 筛选最终状态为closed-lost的记录
        AND milestone_id = 9
        AND closed_type = 2
) AS ml
-- 关联里程碑表获取来源里程碑的名称
JOIN milestones m ON ml.previous_milestone_id = m.id
-- 只保留每个房源的最新记录
WHERE ml.rn = 1
-- 按来源里程碑名称分组统计
GROUP BY m.name
ORDER BY lost_count DESC;

方案说明 #

1. 窗口函数标记最新记录：用ROW_NUMBER()给每个listing_id的记录按时间倒序编号，最新的记录编号为1，精准定位每个房源的最后一条变更记录。
2. 提前筛选缩小范围：在子查询中先过滤出过去一年、最终状态为closed-lost的记录，减少后续关联和计算的数据量，保证性能。
3. 关联里程碑表：筛选出有效最新记录后，再关联milestones表获取previous_milestone_id对应的名称，避免分组时的非聚合列报错问题。
4. 分组统计：最后按里程碑名称分组，统计每个来源里程碑转移到closed-lost的房源数量。

如果你的milestone_logs表没有created_at字段，而是用其他时间字段（比如updated_at），直接替换即可。这个方案不需要修改SQL模式，仅通过单查询完成，性能优于多次查询或冗余子查询的方式。

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