使用CTE和内连接获取最新修改记录的SQL查询错误排查

错误原因 #

你遇到的8120错误，核心是CTE（公共表表达式）的写法违反了SQL聚合查询规则：

• 在LastModified这个CTE里，你选择了Id列，但Id既没有被包含在GROUP BY子句中，也没有用聚合函数（比如MAX()、MIN()）包裹。同一个ProductNumber可能对应多条EventTransaction记录，数据库无法确定要返回哪一条的Id。
• 另外你定义了CTE但主查询完全没有使用它，导致原本想要筛选“每个产品最新记录”的逻辑根本没生效，主查询还是会返回所有EventTransaction记录。

解决方案 #

方案1：用聚合CTE关联筛选最新记录 #

修正CTE并在主查询中关联它，精准获取每个产品最新修改的那条记录：

WITH LastModified AS (
    SELECT
        ProductNumber, MAX(ModifiedON) as Last_Modified
    FROM
        EventTransaction
    GROUP BY
        ProductNumber
)
SELECT
   et.ProductNumber, et.ProductDescription, et.CreatedON, et.CreatedByUser as UserCreated, 
   et.ModifiedON, ua.UserAccountId as UserModified
FROM
    EventTransaction et
INNER JOIN LastModified lm 
    ON et.ProductNumber = lm.ProductNumber AND et.ModifiedON = lm.Last_Modified
INNER JOIN UserAccount ua 
    ON et.ModifiedByUser = ua.UserAccountId
ORDER BY et.ProductNumber

• 调整CTE：只保留ProductNumber和对应的最新修改时间Last_Modified，去掉无意义的Id列。
• 主查询通过ProductNumber+ModifiedON关联CTE，筛选出每个产品的最新记录。

方案2：用窗口函数确保单条返回（推荐） #

如果存在同一个产品在同一时间有多个修改记录的情况，上面的方案会返回多条，用窗口函数可以确保每个产品只返回一条最新记录：

WITH RankedTransactions AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY ProductNumber ORDER BY ModifiedON DESC) AS rn
    FROM
        EventTransaction
)
SELECT
   rt.ProductNumber, rt.ProductDescription, rt.CreatedON, rt.CreatedByUser as UserCreated, 
   rt.ModifiedON, ua.UserAccountId as UserModified
FROM
    RankedTransactions rt
INNER JOIN UserAccount ua 
    ON rt.ModifiedByUser = ua.UserAccountId
WHERE rt.rn = 1
ORDER BY rt.ProductNumber

• 用ROW_NUMBER()窗口函数按ProductNumber分组，每组内按ModifiedON降序排序，最新的记录会被标记为rn=1。
• 通过WHERE rt.rn=1筛选出每组的第一条记录，确保每个产品只返回一行。

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