当使用COUNT与GROUP BY时，MySQL包含零行 [英] MySQL include zero rows when using COUNT with GROUP BY

问题描述

我试图执行一个查询，该查询通过一个名为type_id的属性对一组数据进行分组。

I am trying to perform a query which groups a set of data by an attribute called type_id.

SELECT
vt.id AS voucher_type,
COALESCE(COUNT(v.id), 0) AS vouchers_remaining
FROM
vouchers v
INNER JOIN voucher_types vt
ON vt.id = v.type_id
WHERE
v.sold = 0
GROUP BY vt.id

我想要的结果是type_id和每个类型剩余的未售出产品的数量。这是工作正常，如果有至少一个左，但是如果有一个零计数行，它不会返回结果集。

What I want in the result is the type_id and the number of unsold products remaining for each type. This is working OK provided that there is at least one left, however if there is a zero count row, it is not returned in the result set.

如何设置

任何建议都会非常感谢。

Any advice would be greatly appreciated.

感谢

推荐答案

您必须使用 LEFT JOIN 而不是 INNER JOIN 。您首先选择所有 voucher_types ，然后离开加入以查找计数。

You'll have to use a LEFT JOIN instead of an INNER JOIN. You start by selecting all voucher_types and then left join to find the count.

SELECT
  voucher_types.id AS voucher_type,
  IFNULL(vouchers_count.vouchers_remaining, 0) AS vouchers_remaining
FROM
  voucher_types
LEFT JOIN
  (
    SELECT
    v.type_id AS voucher_type,
    COUNT(v.id) AS vouchers_remaining
    FROM
    vouchers v
    WHERE
    v.sold = 0
    GROUP BY v.type_id
  ) AS vouchers_count
  ON vouchers_count.voucher_type = voucher_types.id

这篇关于当使用COUNT与GROUP BY时，MySQL包含零行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！