SQL多表JOIN时按店铺统计已购与未购去重用户数

你现有查询拿不到正确结果，核心是两个逻辑错误：

• 关联逻辑缺陷：从store表左连purchase再左连user的路径，只会返回在店铺有消费记录的用户，全量未消费用户根本不会进入结果集，无法完成统计
• 指标统计错误：你原SQL里COUNT(DISTINCT purchaseID)统计的是去重订单数，不是你需要的去重购买用户数，和需求不匹配

核心思路是先拿到全平台去重用户总数量，再按店铺统计有消费记录的去重用户数，二者相减就是对应店铺的未消费用户数，可直接运行的SQL如下：

SELECT 
    s.storeID,
    s.storeName,
    COUNT(DISTINCT CASE WHEN p.purchaseID IS NOT NULL THEN p.username END) AS purchases,
    total_user.cnt - COUNT(DISTINCT CASE WHEN p.purchaseID IS NOT NULL THEN p.username END) AS nonPurchases
FROM store s
-- 交叉关联全量用户总数，避免硬编码数值
CROSS JOIN (SELECT COUNT(DISTINCT username) cnt FROM user) total_user
LEFT JOIN purchase p
    ON s.storeID = p.storeID
GROUP BY s.storeID, s.storeName, total_user.cnt;

• 用CROSS JOIN直接获取user表的去重用户总数，不需要写死数值，后续用户表数据变动会自动适配
• 统计购买用户时判断purchase记录非空后取去重username，保证同一个用户在同一家店多笔消费只算1次，符合去重用户的统计要求
• 未购买用户数直接用总用户数减当前店铺购买用户数，完全匹配你给出的预期结果：
  • store1总用户3，购买用户2，未购买1
  • store2总用户3，购买用户0，未购买3
  • store3总用户3，购买用户1，未购买2

如果你的purchase表可能存在不在user表中的脏用户名，可以把左连purchase的部分替换为子查询提前过滤有效用户，避免统计误差：

LEFT JOIN (
    SELECT DISTINCT storeID, username 
    FROM purchase 
    WHERE username IN (SELECT username FROM user)
) p ON s.storeID = p.storeID

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