FilteredVisit表按accountid取最大visitdate的SQL查询异常问题

现有查询的问题分析 #

1. ROW_NUMBER()方案的错误 #

你的ROW_NUMBER()查询完全搞错了分组逻辑：

• partition by visitdate是按访问日期分组，而非按accountid分组，这意味着每个不同的visitdate会生成一组，取每组第一条记录，自然返回远多于1185条的结果，完全不符合“每个accountid取最新记录”的需求。
• 外层排序用的hiw_accountid字段在子查询中未定义，属于语法错误（能运行的话大概率是表中实际存在该字段，但你需求里明确是accountid，应该是笔误）。

2. 子查询MAX()方案的错误 #

这个查询有几个关键问题：

• 子查询表名写错：Filteredhiw_inspection应该是FilteredVisit，关联错误的表直接导致数据结果偏差。
• 字段名不匹配：子查询用了hiw_accountid，外层关联的是S.hiw_accountid，但你需求里表字段是accountid，关联逻辑错误会直接漏掉部分accountid的记录，这就是返回1165条的原因。
• 若存在visitdate为NULL的accountid，MAX(visitdate)会返回NULL，而SQL中WHERE visitdate = NULL永远不成立，这部分accountid会被直接过滤掉。
• 若某个accountid有多个记录的visitdate等于最大日期，这个查询会返回该accountid的多条记录，无法保证每个accountid仅一条结果。

正确的查询方案 #

方案一：修正后的ROW_NUMBER()写法 #

这个方案能保证每个accountid只返回一条记录（即使有多个相同的最大visitdate，会按casereference排序取第一条）：

SELECT accountid, casereference, visitdate
FROM (
    SELECT 
        accountid, 
        casereference, 
        visitdate,
        -- 按accountid分组，按visitdate倒序排序，最新记录的rn=1
        ROW_NUMBER() OVER(PARTITION BY accountid ORDER BY visitdate DESC, casereference) AS rn
    FROM FilteredVisit
) AS T
WHERE rn = 1
ORDER BY accountid;

方案二：修正后的MAX()子查询写法（处理NULL和多记录情况） #

如果需要保留所有最大日期的记录（一个accountid有多个同日期记录时），或者要处理visitdate为NULL的场景，可以用下面的写法：

-- 保留所有最大日期的记录
SELECT S.accountid, S.casereference, S.visitdate
FROM FilteredVisit S
LEFT JOIN (
    SELECT accountid, MAX(visitdate) AS max_visitdate
    FROM FilteredVisit
    GROUP BY accountid
) AS M ON S.accountid = M.accountid
WHERE S.visitdate = M.max_visitdate OR (S.visitdate IS NULL AND M.max_visitdate IS NULL)
ORDER BY S.accountid;

-- 每个accountid仅返回一条（取任意一条同最大日期的记录）
SELECT S.accountid, MAX(S.casereference) AS casereference, S.visitdate
FROM FilteredVisit S
JOIN (
    SELECT accountid, MAX(visitdate) AS max_visitdate
    FROM FilteredVisit
    GROUP BY accountid
) AS M ON S.accountid = M.accountid AND (S.visitdate = M.max_visitdate OR (S.visitdate IS NULL AND M.max_visitdate IS NULL))
GROUP BY S.accountid, S.visitdate
ORDER BY S.accountid;

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