对联合表上每个日期的实例进行计数

Question

>假设我有两个表：Dates和Actions。

我想根据为Dates表选择的所有日期计算有多少行动。

我像这样拉我的日期表：

SELECT DISTINCT
dateid
FROM
dates
WHERE 
DATE(dateid) BETWEEN DATE('2019-01-01') and CURRENT_DATE

在我的Actions桌上，我有现场action_date。 我想数一数一个action_date从dates出现多少次

---

上述查询生成的日期表

+-------+
| Dateid|
+-------+
| Jan 1 |
+-------+
| Jan 2 |
+-------+
| Jan 3 |
+-------+
| Jan 4 |
+-------+

---

按日期列出的操作表

+-------------+
| Action_Date |
+-------------+
| Jan 1       |
+-------------+
| Jan 1       |
+-------------+
| Jan 1       |
+-------------+
| Jan 2       |
+-------------+
| Jan 3       |
+-------------+
| Jan 3       |
+-------------+
| Jan 3       |
+-------------+
| Jan 3       |
+-------------+

---

期望的输出

+-------------+-------+
| Action_Date | Count |
+-------------+-------+
| Jan 1       | 3     |
+-------------+-------+
| Jan 2       | 1     |
+-------------+-------+
| Jan 3       | 4     |
+-------------+-------+
| Jan 4       | 0     |
+-------------+-------+

Answer 1

你似乎想要一个left join：

SELECT d.dateid as ActionDate, COUNT(a.ActionDate)
FROM Dates d LEFT JOIN
Actions a 
ON a.ActionDate = dt.dateid
WHERE DATE(dateid) BETWEEN DATE('2019-01-01') AND CURRENT_DATE
GROUP BY d.dateid
ORDER BY DATE(d.dateid);

Answer 2

(表名和列名可能需要调整(

SELECT a.ActionDate, COUNT(*)
FROM Action_Date a JOIN DateTable dt ON a.ActionDate = dt.dateid
GROUP BY a.ActionDate