mysql - 将不匹配的行计为零 - 如何操作？

Question

这个问题已经有答案了:

MySql Count cannot show 0 values (1 个回答)

已关闭 5 年前。

我想做的是:

根据提供的入住和退房日期获取客房可用性计数。

以下是表格:

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

即将不匹配的行替换为零。如何做到这一点？非常鼓励使用没有嵌套查询的解决方案(因为它们的性能较慢)。

Answer 1

使用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)