SQL分组子查询实现Select TOP 1及分组报错问题咨询

嘿，我来帮你搞定这个需求！你要处理的是wshhistory表中同一assetid下Latest=1的重复记录，最终只保留每组里started最早（按升序取第一条）的那条的Latest=1，其他重复项都改成0对吧？

为什么你之前的SQL报错？ #

你尝试用ROW_NUMBER()窗口函数时搭配了GROUP BY，导致了那个经典的SQL报错——因为GROUP BY要求SELECT列表里的列要么在聚合函数中，要么被包含在GROUP BY子句里，但窗口函数生成的列（比如你的rn）和未聚合的列（比如h.started）不符合这个规则。其实这里完全不需要GROUP BY，窗口函数本身就能帮你完成分组排序的工作。

解决方案：用窗口函数定位要更新的记录 #

我们可以用**CTE（公共表表达式）**或者子查询，先给每条符合条件的记录按assetid分组并排序，然后只更新分组中除第一条以外的记录。

方法1：使用CTE（适用于SQL Server、PostgreSQL、MySQL 8.0+等支持CTE的数据库）

WITH RankedHistory AS (
    SELECT 
        h.uid, -- 这里假设wshhistory有唯一主键uid，用来精准定位记录
        h.assetid,
        ROW_NUMBER() OVER (PARTITION BY h.assetid ORDER BY h.started ASC) AS rn
    FROM wshhistory h
    WHERE h.latest = 1
    -- 只筛选出有至少两条latest=1的assetid，避免无意义的处理
    AND EXISTS (
        SELECT 1
        FROM wshhistory h2
        WHERE h2.assetid = h.assetid
          AND h2.latest = 1
        GROUP BY h2.assetid
        HAVING COUNT(*) > 1
    )
)
UPDATE RankedHistory
SET latest = 0
WHERE rn > 1;

方法2：使用子查询（兼容更多数据库版本）

如果你的数据库不支持直接更新CTE，可以用JOIN子查询的方式：

UPDATE wshhistory h
JOIN (
    SELECT 
        uid,
        ROW_NUMBER() OVER (PARTITION BY assetid ORDER BY started ASC) AS rn
    FROM wshhistory
    WHERE latest = 1
) ranked_records ON h.uid = ranked_records.uid
SET h.latest = 0
WHERE ranked_records.rn > 1;

关键说明 #

• PARTITION BY h.assetid：按资产ID分组，确保我们只在同一资产的记录里排序
• ORDER BY h.started ASC：每组内按started升序排列，这样rn=1的就是最早的那条记录，我们保留它的latest=1；如果你的需求是保留最新的那条（started最大的），只需要把这里改成ORDER BY h.started DESC即可
• 额外的EXISTS子句：用来提前过滤掉没有重复latest=1记录的资产，减少不必要的计算

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