如何基于关联表最新记录的WHERE条件筛选Leads表数据

从Leads表中获取数据时，需要仅针对leads_status_history表中按LSH.leads_status_id DESC排序的最新一条记录应用WHERE条件（例如LSH.status=5）进行筛选，但当前使用的SQL查询会筛选出所有符合该条件的记录，而非仅针对最新单条记录。

预期输出说明 #

• 案例1：当筛选条件为LSH.status=5（该状态为对应记录的最新状态）时，预期输出：

leads_name
---------
Ritaz Texttile

• 案例2：当筛选条件为LSH.status=1时，预期输出0条记录（该状态为旧记录，无匹配）
• 案例3：当筛选条件为LSH.status=2时，预期输出0条记录（该状态为旧记录，无匹配）
• 案例4：当筛选条件为LSH.status=3时，预期输出0条记录（数据库中无对应状态的记录）

尝试的查询语句（案例1） #

select L.leads_name
    from leads L LEFT JOIN leads_user_relation LUR ON LUR.leads_id_fk=L.leads_id 
    LEFT JOIN leads_status_history LSH ON LUR.leads_user_relation_id=LSH.leads_user_relation_id_fk 
    WHERE   LUR.leads_id_fk IS NOT NULL 
    AND (LSH.leads_user_relation_id_fk is NOT null AND LSH.status=5)
    AND LUR.user_id_fk =1 GROUP BY L.leads_id ORDER BY LSH.leads_status_id DESC

表关系 #

解决方案 #

要实现仅针对最新状态记录筛选的需求，可以使用窗口函数ROW_NUMBER()标记每个关联关系对应的最新状态记录，再基于此进行筛选：

SELECT L.leads_name
FROM leads L
JOIN leads_user_relation LUR ON LUR.leads_id_fk = L.leads_id
JOIN (
    SELECT 
        LSH.*,
        -- 按关联关系分组，按状态记录ID降序排序，最新记录标记为1
        ROW_NUMBER() OVER (PARTITION BY LSH.leads_user_relation_id_fk ORDER BY LSH.leads_status_id DESC) AS rn
    FROM leads_status_history LSH
) AS latest_status ON latest_status.leads_user_relation_id_fk = LUR.leads_user_relation_id
WHERE LUR.user_id_fk = 1
  AND latest_status.rn = 1  -- 仅保留最新的状态记录
  AND latest_status.status = 5;  -- 替换为你需要筛选的状态值

逻辑说明 #

1. 子查询中通过ROW_NUMBER()窗口函数，以leads_user_relation_id_fk为分组依据，按leads_status_id降序排序，给每组内的记录分配序号，序号为1的就是该关联关系对应的最新状态记录。
2. 外层查询关联这个子查询，只筛选序号为1的记录，再附加状态条件，就能确保仅针对最新的那条状态记录进行筛选。

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