mysql - 带有聚合函数的子查询 SQL

Question

目标是更新 Inventory 表的 object_category 列，以查找表中项目数量最少的对象 (count(name))

我知道当对 IN 子句使用子查询时，子查询必须返回单个列或值。但是，我不确定如何在保持语法正确的同时实现我的目标。

这是我到目前为止的代码，子查询本身会生成正确的两个要更新的对象，只是当我将其放入 IN 子句时不会生成

UPDATE Inventory
SET object_category = 'ABC'
WHERE Inventory.object_category in (
select count(name) as nameCount , object_category
from Inventory
group by object_category
having nameCount <= min(nameCount));

Answer 1

在 MySQL 中，您需要使用 join 来表达此逻辑。如果您只想更新一个类别，那么这就足够了

UPDATE Inventory i JOIN
       (SELECT count(name) as nameCount, object_category
        FROM Inventory
        GROUP BY object_category
        ORDER BY nameCount
        LIMIT 1
       ) ii
       ON ii.object_category = i.object_category
    SET i.object_category = 'ABC';

您的子查询没有意义并且不起作用。