MySQL：获取各表中每个客户最新行并筛选符合条件的客户

场景与数据示例 #

Customer Status表 #

Account Status表 #

Order Status表 #

需求 #

筛选同时满足以下条件的客户（需先获取每个客户在三张表中的最新记录，即id最大的行，再判断状态）：

• Customer Status最新状态为active
• Account Status最新状态为in arrears
• Order Status最新状态为Pending

示例中仅Stan符合条件。

尝试的错误SQL #

SELECT * FROM customer_status c
WHERE c.id = (SELECT MAX(c2.id) FROM customer_status c2 WHERE c2.name  = c.name) AND c.status = "active"
AND c.name in (SELECT name FROM account_status as WHERE as.id = (SELECT MAX(as2.id) FROM account_status as2 WHERE as.name = as2.name) AND as.active = "in arrears")
AND c.name in (SELECT name FROM order_status os WHERE os.id = (SELECT MAX(os.id) FROM order_status os2 WHERE os.name = os2.name) AND os.status = "Pending")

你的SQL逻辑错误：不是先取每个客户的最新行（最大id）再判断状态，而是先筛选状态符合条件的行，再取其中的最大id，这完全颠倒了逻辑。比如Account Status中Stan的最新行是id=5（状态good standing），但你的子查询会找status='in arrears'的行里的最大id（id=4），导致判断错误。

解决方案 #

不一定非要用Join，但Join写法更清晰易读，能准确实现“先取各表最新记录，再匹配筛选”的逻辑。以下提供两种可行写法：

写法1：子查询+Join（兼容多数数据库） #

SELECT cs.name
FROM (
    -- 取每个客户最新的Customer Status记录
    SELECT name, status
    FROM customer_status
    WHERE id IN (SELECT MAX(id) FROM customer_status GROUP BY name)
) cs
JOIN (
    -- 取每个客户最新的Account Status记录
    SELECT name, status
    FROM account_status
    WHERE id IN (SELECT MAX(id) FROM account_status GROUP BY name)
) acs ON cs.name = acs.name
JOIN (
    -- 取每个客户最新的Order Status记录
    SELECT name, status
    FROM order_status
    WHERE id IN (SELECT MAX(id) FROM order_status GROUP BY name)
) os ON cs.name = os.name
WHERE cs.status = 'active'
  AND acs.status = 'in arrears'
  AND os.status = 'Pending';

写法2：窗口函数（高效简洁，适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库） #

SELECT cs.name
FROM (
    SELECT 
        name, 
        status,
        -- 按客户分组，id倒序排，取第一行（最新记录）
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) rn
    FROM customer_status
) cs
JOIN (
    SELECT 
        name, 
        status,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) rn
    FROM account_status
) acs ON cs.name = acs.name
JOIN (
    SELECT 
        name, 
        status,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) rn
    FROM order_status
) os ON cs.name = os.name
WHERE cs.rn = 1 
  AND acs.rn = 1 
  AND os.rn = 1
  AND cs.status = 'active'
  AND acs.status = 'in arrears'
  AND os.status = 'Pending';

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