Laravel 8中PostgreSQL用子查询结合GROUP BY获取分组行值并统计的方法

原SQL错误原因 #

你的SQL在仅按id_out_sta分组时，SELECT子句中未被聚合、也不属于分组字段的status_exec取值是随机的，不会自动匹配最大date_sta对应的状态值，因此无法得到正确结果。

方案1：窗口函数实现（主流数据库均支持，逻辑清晰） #

WITH ranked_data AS (
    SELECT 
        id_out_sta,
        status_exec,
        -- 按id_out_sta分组，日期倒序排名，排名为1的就是每组最新的记录
        ROW_NUMBER() OVER (PARTITION BY id_out_sta ORDER BY date_sta DESC) AS rn
    FROM public.status_exe 
    JOIN public.order_out ON status_exe.id_out_sta = order_out.id_out
)
SELECT 
    status_exec,
    COUNT(*) AS COUNT
FROM ranked_data
WHERE rn = 1 -- 仅保留每个id_out_sta的最新记录
GROUP BY status_exec
ORDER BY status_exec;

方案2：子查询关联实现（兼容不支持窗口函数的低版本数据库） #

SELECT 
    t.status_exec,
    COUNT(*) AS COUNT
FROM public.status_exe t
JOIN public.order_out o ON t.id_out_sta = o.id_out
-- 关联子查询获取每个id_out_sta的最大日期
JOIN (
    SELECT id_out_sta, MAX(date_sta) AS max_date
    FROM public.status_exe
    GROUP BY id_out_sta
) t_max ON t.id_out_sta = t_max.id_out_sta AND t.date_sta = t_max.max_date
GROUP BY t.status_exec
ORDER BY t.status_exec;

两种方案执行后都能得到你期望的统计结果。

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