如何将含COUNT统计子查询的供应商表SQL改写为INNER JOIN实现语句

原关联子查询可以改写为子查询分组统计后做关联的形式，具体语句如下：

SELECT 
  sup.sup_id,
  sup.sup_name_en AS name,
  cr_stat.cr_numbers
FROM t_sup_supplier AS sup
INNER JOIN (
  SELECT 
    sup_id_from,
    COUNT(sup_id_from) AS cr_numbers
  FROM t_sup_supplier
  GROUP BY sup_id_from
) AS cr_stat ON sup.sup_id = cr_stat.sup_id_from

差异说明 #

• 上述INNER JOIN写法会过滤掉没有任何关联记录的供应商，和你原语句的输出结果不一致：原语句中无关联的供应商cr_numbers会返回0，INNER JOIN会直接丢弃这类记录。如果要完全对齐原语句的输出逻辑，建议使用LEFT JOIN配合空值处理，语句如下：

SELECT 
  sup.sup_id,
  sup.sup_name_en AS name,
  COALESCE(cr_stat.cr_numbers, 0) AS cr_numbers
FROM t_sup_supplier AS sup
LEFT JOIN (
  SELECT 
    sup_id_from,
    COUNT(sup_id_from) AS cr_numbers
  FROM t_sup_supplier
  GROUP BY sup_id_from
) AS cr_stat ON sup.sup_id = cr_stat.sup_id_from

• 如果sup_id_from字段建有索引，分组关联的写法执行效率通常高于原关联子查询写法。

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