如何使用MYSQL按用户更新CUSTOMER表中各订单状态的计数

需求概述 #

基于客户表和订单表，按客户统计不同订单状态的数量：

• COUNT1 对应 PENDING 状态订单数
• COUNT2 对应 CONFIRMED 状态订单数
• COUNT3 对应 CANCELLED 状态订单数

数据表结构 #

客户表（CUSTOMER）

订单表（ORDER）

注：ORDER 是MySQL关键字，需用反引号包裹表名
| SID | CID（客户ID） | STATUS（订单状态） |
|------|--------------|-------------------|
| 5013 | 201 | PENDING |
| 5014 | 201 | PENDING |
| 5015 | 202 | CONFIRMED |
| 5016 | 201 | CANCELLED |
| 5017 | 202 | PENDING |
| 5018 | 201 | CANCELLED |

目标结果 #

解决方案 #

1. 直接查询统计结果 #

通过条件聚合函数实现按客户分组统计：

SELECT 
    c.CustomerID AS `CID（客户ID）`,
    COUNT(CASE WHEN o.STATUS = 'PENDING' THEN 1 END) AS COUNT1,
    COUNT(CASE WHEN o.STATUS = 'CONFIRMED' THEN 1 END) AS COUNT2,
    COUNT(CASE WHEN o.STATUS = 'CANCELLED' THEN 1 END) AS COUNT3
FROM CUSTOMER c
LEFT JOIN `ORDER` o ON c.CustomerID = o.CID
GROUP BY c.CustomerID;

• 使用 LEFT JOIN 保证即使客户没有任何订单，也会出现在结果中（对应COUNT值为0）
• CASE 语句筛选对应状态的订单，COUNT 统计非NULL的记录数

2. 更新客户表的COUNT字段 #

如果需要将统计结果直接更新到客户表的COUNT1/COUNT2/COUNT3字段，可使用关联更新：

UPDATE CUSTOMER c
INNER JOIN (
    SELECT 
        CID,
        COUNT(CASE WHEN STATUS = 'PENDING' THEN 1 END) AS cnt1,
        COUNT(CASE WHEN STATUS = 'CONFIRMED' THEN 1 END) AS cnt2,
        COUNT(CASE WHEN STATUS = 'CANCELLED' THEN 1 END) AS cnt3
    FROM `ORDER`
    GROUP BY CID
) o ON c.CustomerID = o.CID
SET 
    c.COUNT1 = o.cnt1,
    c.COUNT2 = o.cnt2,
    c.COUNT3 = o.cnt3;

• 子查询先统计每个客户的各状态订单数，再通过 INNER JOIN 关联客户表完成更新

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