如何编写单条SQL实现多表关联统计客户订单数与退款数

答案 #

可以编写单条SQL实现需求，具体实现思路和代码如下：

实现思路 #

• 先分别对订单表、退款表按客户ID分组，统计每个客户的订单总数、退款总数
• 以客户表作为主表左关联上述两个预聚合的结果，保证所有客户都能被统计到，没有对应订单/退款的客户对应统计值返回0

实现代码 #

SELECT 
    c.id AS 客户ID,
    COALESCE(o.order_total, 0) AS 订单数量,
    COALESCE(r.refund_total, 0) AS 退款数量
FROM Customer c
LEFT JOIN (
    SELECT customer_id, COUNT(order_id) AS order_total
    FROM `Order`
    GROUP BY customer_id
) o ON c.id = o.customer_id
LEFT JOIN (
    SELECT customer_id, COUNT(refund_id) AS refund_total
    FROM Refund
    GROUP BY customer_id
) r ON c.id = r.customer_id;

注意事项 #

• 因为Order是SQL的保留关键字，查询的时候需要用反引号包裹避免语法报错
• 用COALESCE函数处理没有订单/退款的客户的空值，将空值转为0更符合统计场景的需求
• 如果不需要统计从未产生过订单、也从未产生过退款的客户，可以将主表换成两个子查询的FULL OUTER JOIN结果（注意部分数据库不支持全外连接，可以用UNION先取所有出现过的客户ID再关联）

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