mysql - 在三个表中使用左连接和连接，如果没有找到记录则返回零

Question

我还是新来的，如果我的表中没有找到记录，但我使用的当前查询不起作用，我会尝试返回 0(零)值，所以我已经在数据库中简化了 3 个表:

1.剧场:

+------------+--------------+
| theater_id | theater_name |
+------------+--------------+
|          1 | THEATER 1    |
|          2 | THEATER 2    |
+------------+--------------+

2.演出时间:

+-------------+-----------+
| showtime_id | showtimes |
+-------------+-----------+
|           1 | 10:00 AM  |
|           2 | 2:00 PM   |
+-------------+-----------+

3.交易:

+----------------+---------+-------------+------------+------------+
| transaction_id | seat_no | showtime_id | theater_id |    date    |
+----------------+---------+-------------+------------+------------+
|              1 | 1A      |           1 |          1 | 2017-04-04 |
|              2 | 2A      |           2 |          2 | 2017-04-04 |
|              3 | 3A      |           1 |          1 | 2017-04-04 |
|              4 | 2A      |           1 |          1 | 2017-04-04 |
+----------------+---------+-------------+------------+------------+

查询我目前正在处理它:

SELECT   CONCAT(theater.theater_name,' ',GROUP_CONCAT(DISTINCT showtime.showtimes SEPARATOR ' ')) as 2into1,
         COALESCE(COUNT(*), 0)
FROM     transaction
         LEFT JOIN theater ON theater.theater_id = transaction.theater_id
         LEFT JOIN showtime ON showtime.showtime_id = transaction.showtime_id
WHERE    transaction.date = "2017-04-04"
GROUP BY theater.theater_id,
         showtime.showtime_id
ORDER BY theater.theater_name

查询结果为:

+--------------------+-----------------------+
|      2into1        | coalesce(count(*), 0) |
+--------------------+-----------------------+
| THEATER 1 10:00 AM |                     3 |
| THEATER 2 2:00 PM  |                     1 |
+--------------------+-----------------------+

我真正想要的是，它会提供完整的剧院和放映时间详细信息，如果在交易中没有找到记录，则返回零，我使用 concat 的原因是我要制作一些图表，将 2 分为 1 字段将是 X 轴，count(*) 将是 Y 轴

+--------------------+-----------------------+
|      2into1        | coalesce(count(*), 0) |
+--------------------+-----------------------+
| THEATER 1 10:00 AM |                     3 |
| THEATER 1 2:00 PM  |                     0 |
| THEATER 2 10:00 AM |                     0 |
| THEATER 2 2:00 PM  |                     1 |
+--------------------+-----------------------+

好的，希望你们能理解并帮助我解决这个问题

Answer 1

我认为您需要在剧院和放映时间之间进行交叉连接，然后进行左连接:

SELECT CONCAT(th.theater_name, ' ', sh.showtimes) as 2into1,
       COUNT(t.theater_id)
FROM theater th CROSS JOIN
     showtime st LEFT JOIN
     transaction t
     ON th.theater_id = t.theater_id AND
        st.showtime_id = t.showtime_id AND
        t.date = '2017-04-04'
GROUP BY th.theater_id, st.showtime_id
ORDER BY th.theater_name;

注释:

• showtime_id 位于GROUP BY 中。无需在相关字段上进行GROUP_CONCAT。应该只有一个值。
• 日期和字符串应使用单引号来分隔值
如果没有匹配的行，
• COUNT() 返回 0。 COALESCE() 是多余的。
• 由于 LEFT JOIN，日期条件需要进入 ON 子句。