SQL优化需求：多表关联取最新记录+修正日期差计算

针对你提出的两个需求，我已经调整了原SQL语句，同时保留了所有原字段，下面是具体的实现和说明：

修改后的完整SQL #

SELECT 
    h.target_date, 
    h.flux_id, 
    h.modify_at, 
    f.description, 
    d.nom, 
    d.date_creation, 
    f.ordre, 
    f.name, 
    COALESCE(h.target_date, d.deadline) AS date_cible, 
    -- 修正后的retard计算逻辑
    (EXTRACT(epoch FROM (NOW() - date_cible)) / 86400)::INT AS retard
FROM (
    -- 子查询：为每个name分组，标记最新的modify_at记录
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY f.name ORDER BY h.modify_at DESC) AS rn
    FROM public.flux_historique AS h
    LEFT JOIN public.flux as f 
        INNER JOIN public.document as d ON d.id = :docId 
        ON h.flux_id = f.id 
    WHERE f.id_system_droit_id = d.id_system_droit_id
) AS sub
WHERE sub.rn = 1; -- 只保留每个name组内最新的记录

关键改动说明 #

1. 保留每个name的最新记录 #

我使用了PostgreSQL的窗口函数ROW_NUMBER()来实现这个需求：

• PARTITION BY f.name：将数据按name字段分组
• ORDER BY h.modify_at DESC：在每个分组内按modify_at降序排序，最新的记录会排在第一位
• 外层查询通过WHERE sub.rn = 1筛选出每个分组的第一条记录，也就是每个name对应的最新flux_historique记录

2. 修正retard字段的日期差计算 #

原语句中多余了一层子查询SELECT (NOW() - date_cible)，这会导致计算逻辑冗余且可能出错。修改后直接计算NOW() - date_cible的时间差，通过EXTRACT(epoch FROM ...)将时间差转换为秒数，再除以86400（一天的秒数）得到天数，最后转成整数类型。

如果你只需要日期部分的差值（忽略时分秒），也可以用更简洁高效的写法：

(CURRENT_DATE - date_cible::DATE)::INT AS retard

这个写法会直接用当前日期减去date_cible的日期部分，结果同样是可正可负的天数差值。

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