SQL三字段表自连接补全键值对计数的最优实现方式

优化建议 #

你原有的实现逻辑完全正确，核心思路就是先拿到所有key和value的全量组合，再左关联原表补全计数，我们可以从可读性、语法规范性两个方向做优化：

方案1：替换隐式连接为显式CROSS JOIN（最推荐） #

去掉不必要的CTE嵌套，用显式的笛卡尔连接代替隐式的逗号连接，可读性更高，执行效率和原写法完全一致：

SELECT
  k.key,
  v.value,
  COALESCE(t._count, 0) AS _count
FROM (SELECT DISTINCT key FROM your_table) k
CROSS JOIN (SELECT DISTINCT value FROM your_table) v
LEFT JOIN your_table t 
  ON t.key = k.key AND t.value = v.value

提示：不要用table作为实际表名，它是SQL保留字，会触发语法报错

方案2：大数据量场景的性能优化 #

如果你的表数据量非常大，可以提前给key、value字段建立索引，减少DISTINCT和关联操作的扫描开销。这个方案的计算量由distinct key的数量M、distinct value的数量N决定，只要M*N的规模在可接受范围内，性能就不会有问题。

没有比上述写法更精简的标准SQL实现了，其他依赖特定数据库语法的写法（比如用窗口函数、FULL OUTER JOIN补全）可读性都会差很多，不建议使用。

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