mysql - 计数、排序和加入

Question

我的previous question给了我可以接受的答案

mysql> describe taps;
+------------+-----------+------+-----+-------------------+-------+
| Field      | Type      | Null | Key | Default           | Extra |
+------------+-----------+------+-----+-------------------+-------+
| tag        | int(11)   | NO   |     | NULL              |       |
| station    | int(11)   | NO   |     | NULL              |       |
| time_Stamp | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+------------+-----------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)

并使用查询

SELECT tag
     , COUNT(DISTINCT station) as `visit_count` 
  FROM taps 
 GROUP 
    BY tag 
 ORDER 
    BY COUNT(DISTINCT station) DESC

让访问者按照他们访问过的车站数量进行排序。

现在我想添加

mysql> describe visitors;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| tag_id | int(11) | NO   |     | NULL    |       |
| name   | text    | NO   |     | NULL    |       |
| email  | text    | NO   |     | NULL    |       |
| phone  | text    | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

而且，我不想获取访问者 tag_id，而是想要获取他的 姓名、电子邮件 和 电话 。我知道它涉及JOIN，但就是无法弄清楚:-(

<小时/>

[更新]为了清楚起见，我想输出一个 HTML 表格，按访问最多电台的人排序，显示姓名、电子邮件和电话

Answer 1

SELECT tag
     ,v.email, COUNT(DISTINCT station) as `visit_count` 
  FROM taps as t JOIN visitors as v ON t.tag = v.tag_id
 GROUP 
    BY v.email
 ORDER 
    BY COUNT(DISTINCT station) DESC