如何在GROUP BY查询中关联多表，获取最新版本及用户未签署的协议？

没问题，咱们分两步来解决你的需求——先给现有查询加上每个协议的最新版本信息，再扩展筛选出指定用户未签署最新版本的协议。

一、获取指定机构协议的最新版本信息 #

有两种常用方法，推荐优先用窗口函数的方式，逻辑清晰且能处理版本发布时间重复的场景：

方法1：使用窗口函数ROW_NUMBER() #

这种方式可以给每个协议的版本按发布时间倒序编号，直接取编号为1的最新版本：

SELECT 
    a.id AS agreement_id,
    a.name AS agreement_name,
    v.content AS latest_version_content,
    v.publish_date AS latest_version_publish_date
FROM agreement AS a
JOIN (
    SELECT 
        *,
        -- 按协议分组，发布时间倒序排，最新版本排第1；若时间相同则取id最大的版本
        ROW_NUMBER() OVER (PARTITION BY agreement_id ORDER BY publish_date DESC, id DESC) AS rn
    FROM version
) AS v ON a.id = v.agreement_id AND v.rn = 1
JOIN org AS o ON o.id = a.org_id
WHERE o.name = $1;

注：加上id DESC是为了处理极端情况——如果同一协议有多个版本发布时间完全相同，确保取到最新创建的版本（version表的id是自增主键）。

方法2：用子查询获取最新发布时间 #

如果你的业务场景不会出现同一协议多版本同时间发布的情况，也可以用这种更简洁的方式：

SELECT 
    a.id AS agreement_id,
    a.name AS agreement_name,
    v.content AS latest_version_content,
    v.publish_date AS latest_version_publish_date
FROM agreement AS a
JOIN org AS o ON o.id = a.org_id
JOIN version AS v ON a.id = v.agreement_id
-- 子查询先找出每个协议的最新发布时间
JOIN (
    SELECT agreement_id, MAX(publish_date) AS latest_publish_date
    FROM version
    GROUP BY agreement_id
) AS latest_v ON v.agreement_id = latest_v.agreement_id AND v.publish_date = latest_v.latest_publish_date
WHERE o.name = $1;

二、扩展筛选指定用户未签署最新版本的协议 #

假设你的signatures表包含user_id（关联用户）和version_id（关联版本）字段，我们可以用CTE先统一获取所有协议的最新版本，再左关联签名表筛选未签署的记录：

WITH latest_versions AS (
    SELECT 
        v.id AS version_id,
        v.agreement_id,
        v.content,
        v.publish_date,
        ROW_NUMBER() OVER (PARTITION BY v.agreement_id ORDER BY v.publish_date DESC, v.id DESC) AS rn
    FROM version v
)
SELECT 
    a.id AS agreement_id,
    a.name AS agreement_name,
    lv.content AS latest_version_content,
    lv.publish_date AS latest_version_publish_date
FROM agreement a
JOIN org o ON o.id = a.org_id
JOIN latest_versions lv ON a.id = lv.agreement_id AND lv.rn = 1
-- 左关联用户签署该协议最新版本的记录
LEFT JOIN signatures s ON lv.version_id = s.version_id AND s.user_id = $2
WHERE 
    o.name = $1
    -- 没有匹配到签名记录，说明用户未签署该协议的最新版本（不管有没有签过旧版本）
    AND s.id IS NULL;

这个查询会返回两种情况的协议：

• 用户从未签署过该协议的任何版本；
• 用户签署过该协议的旧版本，但未签署最新版本。

如果你的signatures表结构有差异，只需要调整LEFT JOIN的关联条件即可。

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