MySQL JOIN 盲目

Question

这个查询

   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 |
+------------+--------------+-------------------+

如何将我的查询重写为那个？我想需要某种 JOIN，但我不太明白 :-(

---

[更新]

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)

Answer 1

首先，交换连接的顺序，因此首先对主表进行排序(这仅用于组织目的)。

然后，使用LEFT JOIN 来完成您要查找的内容。这将确保您提取所有 event_stations 记录(联接的左侧部分)，即使 taps 表(联接的右侧部分)中没有相应的记录也是如此.代替缺失的水龙头，您将获得 NULL 值。

COUNT 将忽略聚合中的空值，因此只会返回非空记录的计数。因此，它将为您丢失的 event_stations 记录返回 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;

或者，您可以只对原始连接顺序使用RIGHT JOIN。不过，我个人不喜欢这样做，因为我是 LTR 读者(按顺序排列更重要)。