SQL校验同一customer_id关联多facility_id时pd值是否一致

• 待校验数据表包含三个字段：customer_id、facility_id、pd
• 表结构参考：
  
• 既定业务规则：单个customer_id可关联多个不同的facility_id，但每个customer_id对应的pd值必须全局唯一。
• 校验目标：识别出关联了多条facility_id记录、且同一customer_id下pd值不完全相同的异常数据。

核心校验逻辑：按customer_id维度分组，同时统计分组内关联的设施数量、去重后的pd值数量，筛选出设施数大于1且pd去重计数大于1的分组，即为异常客户。

1. 直接查询异常客户汇总 #

SELECT
  customer_id,
  COUNT(DISTINCT facility_id) AS related_facility_num,
  COUNT(DISTINCT pd) AS different_pd_num
FROM 替换为你的实际表名
GROUP BY customer_id
HAVING related_facility_num > 1
  AND different_pd_num > 1;

2. 查询异常客户对应的全量明细（方便排查问题） #

如果需要定位到具体哪条记录的pd值不一致，可以关联原表拉取明细：

SELECT ori.*
FROM 替换为你的实际表名 ori
INNER JOIN (
  SELECT customer_id
  FROM 替换为你的实际表名
  GROUP BY customer_id
  HAVING COUNT(DISTINCT facility_id) > 1
    AND COUNT(DISTINCT pd) > 1
) abnormal_cust ON ori.customer_id = abnormal_cust.customer_id
ORDER BY ori.customer_id, ori.facility_id;

注意事项：如果pd字段允许存NULL值，COUNT(DISTINCT pd)会将NULL计为一个独立值。如果业务规则中NULL属于无效脏数据，可在查询最内层增加WHERE pd IS NOT NULL过滤条件后再做分组统计。

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