使用GROUP BY统计同色对应人数的SQL正确性校验及优化咨询

原SQL存在的问题 #

• 语法错误：FROM子句重复书写了from关键字，SQL无法正常解析执行。
• 逻辑错误：COUNT(*)统计的是每个颜色对应的总行数，你提供的样例数据中蓝色对应4行（Sally有2条蓝色记录），直接统计会得到蓝色人数为4，和你预期的3个不同人的结果不符，不符合「统计相同颜色的人数」的需求。

正确实现方式 #

修正语法错误，同时用COUNT(DISTINCT Person)对同色下的人名去重统计，即可得到符合预期的结果：

SELECT
  Color, COUNT(DISTINCT Person) as number_people
FROM
  table_a
GROUP BY Color
HAVING number_people > 1

如果所用数据库不支持在HAVING子句中引用SELECT定义的别名，可以改成如下兼容写法：

SELECT
  Color, COUNT(DISTINCT Person) as number_people
FROM
  table_a
GROUP BY Color
HAVING COUNT(DISTINCT Person) > 1

优化说明 #

上述写法已经是当前需求下的最优实现：

• 逻辑直观清晰，直接匹配需求，后续维护成本低
• 只要在Color和Person列上建立联合索引，即可完全走索引执行，不需要全表扫描，性能最优
• 相比用子查询、窗口函数等其他实现方式，执行计划更简洁，开销更低

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