如何用MySQL在phpMyAdmin中统计三张表各推荐人客户数及佣金总和

正确查询语句 #

写法1：关联表聚合（性能更优，推荐）

SELECT 
    r.id AS referrer_id,
    r.referrer_name,
    COUNT(DISTINCT c.id) AS total_client,
    IFNULL(SUM(cm.commission), 0) AS total_commission
FROM referrer r
LEFT JOIN client c ON r.id = c.referrer_id
LEFT JOIN commission cm ON c.id = cm.client_id
GROUP BY r.id, r.referrer_name
ORDER BY r.id;

写法2：子查询修正版（和你原有写法逻辑更接近）

SELECT 
    r.id AS referrer_id,
    r.referrer_name,
    (SELECT COUNT(*) FROM client WHERE referrer_id = r.id) AS total_client,
    (SELECT IFNULL(SUM(commission), 0) 
     FROM commission cm 
     INNER JOIN client c ON cm.client_id = c.id 
     WHERE c.referrer_id = r.id) AS total_commission
FROM referrer r
ORDER BY r.id;

原查询出错原因 #

1. 统计佣金的子查询仅关联了外层的client.id，没有按推荐人维度聚合，加上你对referrer和client做了左连接后，同个推荐人存在多个客户时会返回重复行，统计结果不符合预期
2. 没有处理无佣金场景的null值，无佣金的推荐人统计结果会返回null，而不是要求的0

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