将不匹配的行计为零

将不匹配的行计为零 - 如何？(Count non-matching rows as zero - how to? [duplicate])

这个问题在这里已经有了答案：

MySql计数不能显示0值 1答案

我试图做的事情是：

根据提供的登记和结帐日期提取房间可用性计数。

这里是表格：

categories id | name 1 | Luxe 2 | Deluxe reservations id | category_id | checkin | checkout 1 | 1 | 2018-01-06 | 2018-01-10 1 | 2 | 2018-01-11 | 2018-01-13 3 | 2 | 2018-01-13 | 2018-01-17

预期产出

由于category_id = 2从2018-01-06到2018-01-10可用（请参阅reservations表中的第一行），因此在提取总数时，我试图包含其可用性计数。

所以当运行这个查询时

SELECT categories.name AS category, COUNT(reservations.id) AS free_count FROM reservations INNER JOIN categories ON categories.id = reservations.category_id WHERE checkin BETWEEN '2018-01-06' AND '2018-01-10' GROUP BY categories.name, reservations.id

输出是：

category | free_count Luxe | 1

不过，我愿意输出为：

category | free_count Luxe | 1 Deluxe | 0

即将不匹配的行替换为零。 如何做到这一点？ 非常鼓励没有嵌套查询的解决方案（由于性能低下）。

This question already has an answer here:

MySql Count cannot show 0 values 1 answer

The thing, that I'm trying to do is:

Fetch room availability count based on supplied checkin and checkout dates.

Here are the tables:

categories id | name 1 | Luxe 2 | Deluxe reservations id | category_id | checkin | checkout 1 | 1 | 2018-01-06 | 2018-01-10 1 | 2 | 2018-01-11 | 2018-01-13 3 | 2 | 2018-01-13 | 2018-01-17

Expected output

Since category_id = 2 is available from 2018-01-06 up to 2018-01-10 (see the very first row in reservations table), I'm trying to include its availability count, when fetching total count.

So when running this query

SELECT categories.name AS category, COUNT(reservations.id) AS free_count FROM reservations INNER JOIN categories ON categories.id = reservations.category_id WHERE checkin BETWEEN '2018-01-06' AND '2018-01-10' GROUP BY categories.name, reservations.id

The output is:

category | free_count Luxe | 1

However I'm willing the output to be:

category | free_count Luxe | 1 Deluxe | 0

i.e replace non-matching rows to zero. How this can be done? A solution without nested queries (due to their slow performance) is very encouraged.

最满意答案

使用LEFT JOIN而不是INNER JOIN

SELECT c.name AS category, COUNT(r.id) AS free_count FROM categories c LEFT JOIN reservations r ON c.id = r.category_id AND checkin BETWEEN '2018-01-06' AND '2018-01-10' GROUP BY c.name

将checkin过滤器保持在ON状态是很重要的。 当你在Where子句中保留正确的表过滤器时，不匹配的记录将有NULL值，由于where条件（基本上Left join将被转换为Inner join ）

Use LEFT JOIN instead of INNER JOIN

SELECT c.name AS category, COUNT(r.id) AS free_count FROM categories c LEFT JOIN reservations r ON c.id = r.category_id AND checkin BETWEEN '2018-01-06' AND '2018-01-10' GROUP BY c.name

Its important to keep the checkin filter in ON condition. When you keep the right table filter in Where clause, the non matching records will have NULL values which will be filtered in result due the where condition(basically Left join will be converted to Inner join)