MySQL统计近3个月用户订单数更新字段报错解决

你原来的SQL有三个核心问题：

• 赋值给no_of_orders字段的子查询返回了2列数据（日期判断结果、COUNT(*)统计值），单字段赋值仅支持接收单列返回值，直接触发"Operand should only contain one column"报错
• 近3个月的时间判断逻辑没有写在WHERE筛选条件里，即使不报错，也会统计用户所有历史订单，不符合需求
• 用EXTRACT(month from created_date)提取月份做时间判断的逻辑本身有缺陷，会出现跨年统计错误，比如去年同月份的订单会被误判定为近3个月的有效订单

方案1：简单关联子查询写法 #

逻辑直观，适合数据量较小的场景：

UPDATE users u 
SET no_of_orders = (
    SELECT COUNT(*)
    FROM orders o
    WHERE 
        o.created_by = u.user_id
        AND o.created_date >= NOW() - INTERVAL 3 MONTH
);

注意：近3个月没有订单的用户，执行后no_of_orders会被更新为NULL，如果需要统一设为0，可以把赋值部分改成IFNULL((SELECT COUNT(*) FROM orders o WHERE o.created_by = u.user_id AND o.created_date >= NOW() - INTERVAL 3 MONTH), 0)

方案2：预聚合关联更新写法 #

性能更高，适合订单表数据量较大的场景：

UPDATE users u
LEFT JOIN (
    SELECT created_by, COUNT(*) AS order_cnt
    FROM orders
    WHERE created_date >= NOW() - INTERVAL 3 MONTH
    GROUP BY created_by
) o ON u.user_id = o.created_by
SET u.no_of_orders = IFNULL(o.order_cnt, 0);

这个写法会先一次性聚合出所有用户近3个月的订单总数，再和用户表做关联更新，不需要逐行执行子查询，执行效率远高于逐行关联的写法，同时会自动把近3个月无订单的用户计数字段设为0，不需要额外处理空值。

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