SQL如何关联两张表统计每个国家不同客户状态的数量

解决SQL #

你要实现的按国家统计各状态客户数的需求，可以用条件聚合实现行转列统计，写法如下：

SELECT 
    c.country AS Country,
    COUNT(CASE WHEN s.status = 'Not Started' THEN 1 END) AS `Not Started`,
    COUNT(CASE WHEN s.status = 'In Progress' THEN 1 END) AS `In Progress`,
    COUNT(CASE WHEN s.status = 'Done' THEN 1 END) AS `Done`
FROM customers_customers c
LEFT JOIN customer_status s
ON c.customerID = s.customerID
GROUP BY c.country;

逻辑说明 #

• 先关联两张表拿到所有客户对应的国家、状态数据
• 按国家字段分组，每个国家仅返回一行结果
• 通过CASE WHEN匹配对应状态，匹配成功的会被COUNT统计，失败的返回NULL不计入总数，最终得到每个国家下不同状态的客户数量

如果你使用的是MySQL数据库，还可以用更简洁的写法：

SELECT 
    country AS Country,
    SUM(s.status = 'Not Started') AS `Not Started`,
    SUM(s.status = 'In Progress') AS `In Progress`,
    SUM(s.status = 'Done') AS `Done`
FROM customers_customers c
LEFT JOIN customer_status s ON c.customerID = s.customerID
GROUP BY country;

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