MySQL实现按客户按月统计订单数（无订单显示0）

需求说明 #

统计每个客户每个月的OrderId数量，无订单时显示0，需覆盖所有客户及订单中出现的所有月份。

表结构与测试数据 #

Customer表 #

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY
);

INSERT INTO Customer (CustomerID) VALUES (1), (2), (3);

Order表 #

CREATE TABLE `Order` (
    OrderId INT PRIMARY KEY,
    CustomerID INT,
    `Date` DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

INSERT INTO `Order` (OrderId, CustomerID, `Date`) VALUES
(1, 1, '2022-01-02'),
(2, 1, '2022-01-04'),
(3, 2, '2022-02-03'),
(4, 2, '2022-03-03');

查询SQL语句 #

WITH MonthList AS (
    -- 提取订单表中所有不重复的月份，格式化为YYYY-MM
    SELECT DISTINCT DATE_FORMAT(`Date`, '%Y-%m') AS month
    FROM `Order`
),
CustomerMonth AS (
    -- 生成所有客户与所有月份的组合，确保无遗漏
    SELECT c.CustomerID, ml.month AS `Date`
    FROM Customer c
    CROSS JOIN MonthList ml
)
-- 左连接订单表统计订单数，无订单时COUNT返回0
SELECT 
    cm.CustomerID,
    cm.`Date`,
    COUNT(o.OrderId) AS CountOrderID
FROM CustomerMonth cm
LEFT JOIN `Order` o 
    ON cm.CustomerID = o.CustomerID 
    AND DATE_FORMAT(o.`Date`, '%Y-%m') = cm.`Date`
GROUP BY cm.CustomerID, cm.`Date`
ORDER BY cm.`Date`, cm.CustomerID;

逻辑说明 #

1. MonthList：从订单表中提取所有存在的月份，避免遗漏任何有订单的月份。
2. CustomerMonth：通过交叉连接生成每个客户与每个月份的笛卡尔积，保证每个客户每个月都有一条记录。
3. 左连接统计：左连接订单表后，用COUNT(o.OrderId)统计订单数——左连接中无匹配订单记录时，OrderId为NULL，COUNT不会统计NULL值，自然返回0。

正确查询结果 #

CustomerID  Date    CountOrderID
1           2022-01 2
2           2022-01 0
3           2022-01 0
1           2022-02 0
2           2022-02 1
3           2022-02 0
1           2022-03 0
2           2022-03 1
3           2022-03 0

注：你提供的预期结果中2022-01的CustomerID2的CountOrderID为1，与测试数据不符，实际应为0，上述结果为正确统计结果。

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