SQL多表查询问题：筛选含'CRED'行数多于'PUR'的记录

首先，你原来的写法存在两个核心问题：

• WHERE子句无法直接用聚合函数做过滤，因为WHERE是在分组/聚合计算之前执行的，而聚合函数的结果是分组后的统计值，这类过滤得用HAVING子句来处理。
• 你的子查询没有关联到具体客户，统计的是全表的CRED和PUR数量，而非每个客户对应的数量，这会导致过滤逻辑完全不符合需求。

下面是修正后的SQL代码，我用更清晰的JOIN语法替代了旧式逗号连接，同时实现了按客户统计并过滤的逻辑：

SELECT 
  lots_of_stuff.*, 
  A.more_stuff, 
  B.stuff, 
  C.things,
  -- 可选：返回统计数量，方便验证逻辑是否正确
  SUM(CASE WHEN C.things LIKE '%CRED%' THEN 1 ELSE 0 END) AS cred_count,
  SUM(CASE WHEN C.things LIKE '%PUR%' THEN 1 ELSE 0 END) AS pur_count
FROM 
  table A
JOIN 
  table B ON B.more_stuff = A.stuff
JOIN 
  table C ON -- 这里需要补充C表与其他表的关联条件！比如 C.customer_id = A.customer_id（假设客户标识在A表）
WHERE 
  [其他条件]
GROUP BY 
  -- 需列出所有非聚合的SELECT字段，或根据数据库规则调整（比如PostgreSQL的DISTINCT ON）
  lots_of_stuff.*, A.more_stuff, B.stuff, C.things
HAVING 
  SUM(CASE WHEN C.things LIKE '%CRED%' THEN 1 ELSE 0 END) > SUM(CASE WHEN C.things LIKE '%PUR%' THEN 1 ELSE 0 END)

关键细节解释： #

1. 显式JOIN替代逗号连接：旧式逗号连接容易产生意外笛卡尔积，用显式JOIN并指定关联条件更清晰，也能避免表关联错误。务必补充C表与其他表的关联条件（比如客户ID关联），否则会把C表所有数据和A、B表强制关联，这肯定不是你要的结果。
2. 条件聚合统计数量：用SUM(CASE...)统计每个客户下符合条件的条目数：
   • 当C.things包含'CRED'时返回1，否则返回0，SUM后就是该客户的CRED条目总数。
   • 同理统计PUR的数量。你也可以换成COUNT(CASE WHEN C.things LIKE '%CRED%' THEN 1 END)，因为CASE不满足条件时返回NULL，COUNT不会统计NULL值，效果完全一致。
3. GROUP BY分组：必须按客户的唯一标识（以及SELECT中所有非聚合字段）分组，这样聚合函数才会计算每个客户的专属统计值。
4. HAVING子句过滤：在分组和聚合计算完成后，用HAVING筛选出CRED数量大于PUR数量的客户记录，这才是聚合结果过滤的正确方式。

如果你的数据库支持窗口函数，还可以用窗口函数先计算每个客户的统计值，再过滤（适合需要保留所有明细行的场景）：

SELECT *
FROM (
  SELECT 
    lots_of_stuff.*, 
    A.more_stuff, 
    B.stuff, 
    C.things,
    SUM(CASE WHEN C.things LIKE '%CRED%' THEN 1 ELSE 0 END) OVER (PARTITION BY A.customer_id) AS cred_count,
    SUM(CASE WHEN C.things LIKE '%PUR%' THEN 1 ELSE 0 END) OVER (PARTITION BY A.customer_id) AS pur_count
  FROM 
    table A
  JOIN 
    table B ON B.more_stuff = A.stuff
  JOIN 
    table C ON C.customer_id = A.customer_id
  WHERE 
    [其他条件]
) AS subquery
WHERE cred_count > pur_count

这种方式会保留每个符合条件的客户的所有明细行，而GROUP BY的方式如果SELECT了明细字段，可能会返回重复的客户行（取决于分组字段），你可以根据实际需求选择。

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