配置单元计数另一个表中匹配的行数

Question

表1：

| ID | Timestamp
| 1  | 1970
| 2  | 1971

表2：

| Timestamp |
| 1970      |
| 1970      |
| 1970      |
| 1971      |
| 1971      |

如何执行以这样一种方式连接表的查询：

| ID | Timestamp | Count
| 1  | 1970      | 3
| 2  | 1971      | 2

基本上join on timestamp，但只是为了得到一个计数？

Answer 1

select  t1.ID
,t1.`Timestamp`
,coalesce (t2.`Count`,0) as `Count`
from    Table1 t1
left join  (select      `Timestamp`
,count(*)    as `Count`
from        Table2
group by    `Timestamp`
) t2
on          t2.`Timestamp` = t1.`Timestamp`

---

+----+-----------+-------+
| id | timestamp | count |
+----+-----------+-------+
|  1 |      1970 |     3 |
|  2 |      1971 |     2 |
+----+-----------+-------+