mysql - mysql group by 字段计算记录数

Question

我在 MySQL 中的表 http://joxi.ru/5mdWRV8tyQzyr1

我的程序传递数组用户

$ids = [1, 3, 7];

我的表查询:

SELECT responsible_id, count(id) as count
from test
WHERE active = 1
  AND status = 3
  AND responsible_id in (1, 3, 7)
GROUP BY responsible_id
ORDER BY count(id)

我得到结果http://joxi.ru/vAWYGq0IMxdjmW

但是，如果表中不存在，我还需要第一行 Responsible_id = 7 且 count = 0。

Answer 1

要执行您想要的操作，请使用左连接:

SELECT v.responsible_id, count(t.id) as count
FROM (SELECT 1 as responsible_id UNION ALL
      SELECT 3 as responsible_id UNION ALL
      SELECT 7 as responsible_id
     ) v LEFT JOIN
     test t
     ON t.responsible_id = v.responsible_id AND
        t.active = 1 AND
        t.status = 3
GROUP BY v.responsible_id
ORDER BY count(id);

请注意，WHERE 中的条件已移至 ON 子句。