mysql - MySQL JOIN失明

此查询

   SELECT station_id, station_name,
        COUNT(event_station) as `total_visit_count`
           FROM taps AS t
           JOIN event_stations AS s
              ON t.event_station = s.station_id
                 WHERE s.event_id=6
                    GROUP BY s.station_id
                    ORDER BY s.station_id;

回报

+------------+--------------+-------------------+
| station_id | station_name | total_visit_count |
+------------+--------------+-------------------+
|          5 | Station one  |                24 |
|          6 | Station two  |                35 |
|          7 | St. Pancras  |                34 |
+------------+--------------+-------------------+

这很好。
但是，taps中有一些站点尚未被访问，我希望它们显示为zer0的total_visit_count。

+------------+--------------+-------------------+
| station_id | station_name | total_visit_count |
+------------+--------------+-------------------+
|          5 | Station one  |                24 |
|          6 | Station two  |                35 |
|          7 | St. Pancras  |                34 |
|          8 | Station four |                 0 |
+------------+--------------+-------------------+

如何将查询重写为该查询？我想需要某种连接，但我看不太清楚：-(
[更新]

describe event_Stations;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| station_id   | int(11)    | NO   | PRI | NULL    | auto_increment |
| event_id     | int(11)    | NO   |     | NULL    |                |
| station_name | text       | NO   |     | NULL    |                |
| allocated    | tinyint(1) | NO   |     | 0       |                |
+--------------+------------+------+-----+---------+----------------+
4 rows in set (0.20 sec)


describe taps;

+---------------+-----------+------+-----+-------------------+-------+
| Field         | Type      | Null | Key | Default           | Extra |
+---------------+-----------+------+-----+-------------------+-------+
| tag_id        | int(11)   | NO   |     | NULL              |       |
| time_stamp    | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
| event_station | int(11)   | NO   |     | NULL              |       |
| device_id     | text      | YES  |     | NULL              |       |
| device_type   | text      | YES  |     | NULL              |       |
| event_id      | int(11)   | NO   |     | NULL              |       |
+---------------+-----------+------+-----+-------------------+-------+
6 rows in set (0.00 sec)


select * from event_stations where event_id=6;
+------------+----------+-----------------+-----------+
| station_id | event_id | station_name    | allocated |
+------------+----------+-----------------+-----------+
|          5 |        6 | Station one     |         0 |
|          6 |        6 | Station two     |         0 |
|          7 |        6 | St. Pancras     |         0 |
|          8 |        6 | Station three   |         0 |
|          9 |        6 | Station four    |         0 |
|         10 |        6 | Station five    |         0 |
|         11 |        6 | Station six     |         0 |
|         12 |        6 | Station seven   |         0 |
|         13 |        6 | Station eight   |         0 |
|         14 |        6 | Station nine    |         0 |
|         15 |        6 | Station  ten    |         0 |
|         16 |        6 | Station  eleven |         0 |
+------------+----------+-----------------+-----------+
12 rows in set (0.00 sec)

最佳答案

首先，交换连接的顺序，以便首先对主表进行排序（这仅用于组织目的）。
然后，使用左连接来完成您要查找的内容。这将确保拉取所有event_stations记录（join的左侧部分），即使taps表（join的右侧部分）中没有相应的记录。代替丢失的taps，您将得到空值。
COUNT将忽略聚合中的空值，因此将只返回非空记录的计数。因此，它将为您丢失的事件站点记录返回0。

SELECT
    station_id,
    station_name,
    COUNT(event_station) as `total_visit_count`
FROM event_stations AS s
   LEFT JOIN taps AS t
      ON t.event_station = s.station_id
WHERE s.event_id = 6
GROUP BY s.station_id
ORDER BY s.station_id;

或者，您可以在原始联接顺序中使用右联接。不过，我个人不喜欢这样做，因为我是一个LTR读者（首先顺序更重要）。

Taps

mysql - MySQL JOIN失明