MySQL数据提取：查询符合特定条件的最新deals记录及SQL问题

表数据与需求说明 #

现有deals表，示例数据如下：

id deal_ref objectname  status
1  1234      tom        correct
2  1234      tom        correct
3  1234      jerry      wrong
4  1234      tom        correct

需求：

• 识别所有状态为correct的最新deal记录
• 需确保全表最大ID对应的记录，其deal_ref与目标记录的deal_ref一致，且状态为correct

原SQL存在的问题 #

你写的SQL有多处错误：

• 聚合函数使用错误：max(id)是聚合函数，不能直接放在WHERE子句中，必须通过子查询获取最大值后再匹配
• 逻辑匹配错误：d.deal_ref = max(id)完全不合理，deal_ref是业务编号（如示例中的1234），id是自增主键，两者类型、含义均不同，不可能相等
• 字段/表名不匹配：原表字段是status，你写成了deal_status；表名是deals，你用了dealv1，需统一
• 状态值不符：需求是筛选correct状态，你写的是'Prospect'，完全偏离需求

修正后的SQL #

场景1：获取每个符合条件的deal_ref中，状态为correct的最新记录 #

（满足全表最大ID的deal_ref对应记录状态为correct，且取该deal_ref下最新的correct记录）

WITH max_id_info AS (
    -- 获取全表最大ID对应的deal_ref和状态
    SELECT deal_ref, status
    FROM deals
    WHERE id = (SELECT MAX(id) FROM deals)
),
ranked_deals AS (
    -- 按deal_ref分组，给每条correct记录按ID倒序排名
    SELECT 
        id,
        deal_ref,
        objectname,
        status,
        ROW_NUMBER() OVER (PARTITION BY deal_ref ORDER BY id DESC) AS rn
    FROM deals
    WHERE status = 'correct'
      AND date_created BETWEEN '2022-11-02 00:00:00' AND '2022-11-04 00:00:00'
)
-- 选出每个deal_ref下排名第一的记录，且该deal_ref在max_id_info中状态为correct
SELECT id, deal_ref, objectname, status
FROM ranked_deals
WHERE rn = 1
  AND deal_ref IN (SELECT deal_ref FROM max_id_info WHERE status = 'correct');

场景2：仅获取全表最大ID且状态为correct的记录 #

如果你的需求只是取全表最后一条（最大ID）且状态为correct的记录，用这个更简单：

SELECT id, deal_ref, objectname, status
FROM deals
WHERE id = (SELECT MAX(id) FROM deals)
  AND status = 'correct'
  AND date_created BETWEEN '2022-11-02 00:00:00' AND '2022-11-04 00:00:00';

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