mysql - 使用 `count` 在 `group by` 上获取值为 0 的列

Question

我正在使用此查询来获取按cmp2字段分组的行数，但我需要为每个获取一列，cmp2即使它的总和结果是0. 我不能这样理解：

SELECT CMP2, COALESCE(count(*), 0) as count 
FROM datos_con851_0,
     datos_con851_1  
WHERE datos_con851_0.REGISTRO = datos_con851_1.REGISTRO 
  AND SPEEDER = 1
GROUP BY CMP2;

标签： mysqlsql

Answer 1

你想要一个left join，大概是这样的：

SELECT d0.CMP2, count(d1.REGISTRO) as count 
FROM datos_con851_0 d0 left join
     datos_con851_1 d1
     on d0.REGISTRO = d1.REGISTRO AND
        d1.SPEEDER = 1  -- just guess this comes from `d1`
GROUP BY d0.CMP2;