MySQL多条件多统计查询合并：客户与预约表省份维度统计需求

表结构 #

客户表（customer） #

预约表（appointment） #

合并后的查询语句 #

SELECT
    c.province_id,
    COUNT(CASE WHEN c.created_day BETWEEN '2023-12-01' AND '2023-12-31' THEN 1 END) AS totalLead,
    COUNT(DISTINCT CASE WHEN a.appointment_date BETWEEN '2023-12-01' AND '2023-12-31' THEN c.id END) AS totalAppointment,
    COUNT(DISTINCT CASE WHEN a.appointment_date BETWEEN '2023-12-01' AND '2023-12-31' AND a.accepted = 1 THEN c.id END) AS totalAppointmentAccepted
FROM
    customer c
LEFT JOIN
    appointment a ON c.id = a.customer_id
WHERE
    c.province_id IS NOT NULL
GROUP BY
    c.province_id;

说明 #

• totalLead：通过CASE WHEN筛选2023年12月新增的客户，用COUNT统计数量，与原查询1逻辑一致
• totalAppointment：统计有12月预约记录的客户数（去重），对应原查询2的分组去重逻辑
• totalAppointmentAccepted：统计有12月已接受预约记录的客户数（去重），对应原查询3的逻辑
• 使用LEFT JOIN确保所有含省份信息的客户都被纳入统计，避免遗漏无预约记录的省份

内容的提问来源于stack exchange，提问作者Đào Văn Mong