如何在UPDATE语句IN子句中正确使用含聚合函数的子查询

嘿，我明白你现在遇到的问题了——单独运行子查询能拿到正确的两个对象，但把它放进IN子句里UPDATE就跑不起来对吧？大概率是子查询的结构没写对，导致返回的不是IN需要的单列标识值。下面给你两种可行的写法，帮你搞定这个需求：

方法一：使用窗口函数（推荐，直观处理并列最小值） #

窗口函数RANK()可以帮我们直接给每个对象按count(name)排序，轻松拿到所有排名第一（也就是数量最少）的对象：

UPDATE Inventory
SET object_category = '你要设置的目标类别' -- 替换成实际要更新的类别值
WHERE object_id IN (
    SELECT object_id
    FROM (
        SELECT 
            object_id,
            COUNT(name) AS name_count,
            -- 按count(name)升序排序，数量最少的排第1
            RANK() OVER (ORDER BY COUNT(name) ASC) AS rnk
        FROM Inventory
        GROUP BY object_id
    ) AS ranked_objects
    WHERE rnk = 1
);

如果你的场景里不需要保留并列的最小值（比如只更新其中一个），可以把RANK()换成ROW_NUMBER()。

方法二：嵌套子查询匹配最小count值 #

先算出所有对象里最小的count(name)值，再匹配出所有等于这个值的对象：

UPDATE Inventory
SET object_category = '你要设置的目标类别'
WHERE object_id IN (
    SELECT object_id
    FROM Inventory
    GROUP BY object_id
    HAVING COUNT(name) = (
        SELECT MIN(name_count)
        FROM (
            SELECT COUNT(name) AS name_count
            FROM Inventory
            GROUP BY object_id
        ) AS count_subquery
    )
);

为什么之前的写法会失败？ #

通常出现这种情况的原因是：你的子查询可能返回了多列数据（比如同时返回了object_id和count(name)），但IN子句要求子查询必须返回单列值。上面两种写法都确保了子查询只返回需要的object_id列，所以能正常工作。

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