子查询返回多行原因及UPDATE查询中GROUP BY的正确用法咨询

1. 子查询返回多行的原因 #

你的子查询中使用了GROUP BY u.user_id，这会按照每个用户ID分组统计进行中的订单数量，最终为每个用户生成一行统计结果，因此整个子查询返回多行数据。而UPDATE语句里SET u.active_orders = (...)要求右侧子查询只能返回单个值，用于赋值给当前被更新的用户记录，多行结果会直接导致执行报错。

2. UPDATE中正确使用GROUP BY的方案 #

要实现为每个用户设置其进行中的订单数量，有两种常用的正确写法：

方案一：关联外层用户表的子查询 #

修改子查询，添加与外层user表的关联条件，让子查询仅返回当前被更新用户的统计值：

UPDATE user u 
SET u.active_orders = (
    SELECT COUNT(o.order_status) 
    FROM goods g 
    INNER JOIN order_details o ON g.goods_id = o.goods_id 
    WHERE g.master_id = u.user_id  -- 关联外层当前用户的ID
      AND o.order_status = 'IN_PROGRESS'
);

注：如果用户没有任何进行中的订单，COUNT会返回0，符合需求；若要避免NULL值，可使用COALESCE(COUNT(...), 0)确保赋值为0。

方案二：使用UPDATE JOIN（更高效） #

通过JOIN直接将统计结果与用户表关联，一次性完成更新，性能比子查询更优：

UPDATE user u
JOIN (
    SELECT g.master_id AS user_id, COUNT(o.order_status) AS active_count
    FROM goods g
    INNER JOIN order_details o ON g.goods_id = o.goods_id
    WHERE o.order_status = 'IN_PROGRESS'
    GROUP BY g.master_id
) AS order_stats ON u.user_id = order_stats.user_id
SET u.active_orders = order_stats.active_count;

如果需要给没有进行中订单的用户也设置0，可改用LEFT JOIN：

UPDATE user u
LEFT JOIN (
    SELECT g.master_id AS user_id, COUNT(o.order_status) AS active_count
    FROM goods g
    INNER JOIN order_details o ON g.goods_id = o.goods_id
    WHERE o.order_status = 'IN_PROGRESS'
    GROUP BY g.master_id
) AS order_stats ON u.user_id = order_stats.user_id
SET u.active_orders = COALESCE(order_stats.active_count, 0);

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