Mysql - JOIN 两个表按 2 个日期分组

Question

我有两个未通过外键链接但我想加入的表。

这是我想加入的两个功能性 SQL 查询:

SELECT DATE(added) as 'day', COUNT(*) as 'TopicSub' 
FROM user_subscription 
WHERE topic_id = 39 
GROUP BY DATE(added)


SELECT DATE(date) as 'day', COUNT(*) as 'QSub' 
FROM user_submitted_q 
WHERE question_id IN (SELECT id FROM questions WHERE topic_id = 39) 
GROUP BY DATE(date)

这两个查询返回的结果包括按天分组的条目总数。

首先关注两个查询的天数不一定匹配(期间的所有天数都没有条目)，我想合并结果，如果另一列不包含等效日期，则为 0。

在对所有可能的关节进行了大量不成功的尝试之后，我发现这个查询可能适用于 PostgreSQL:

SELECT * 
FROM 
    (SELECT DATE(date) as 'day', COUNT(*) as 'QSub' 
     FROM user_submitted_q 
     WHERE question_id IN (SELECT id FROM questions WHERE topic_id = 39) 
     GROUP BY DATE(date)) AS q1 
FULL OUTER JOIN  
    (SELECT DATE(added) as 'day', COUNT(*) as 'TopicSub' 
     FROM user_subscription 
     WHERE topic_id = 39 
     GROUP BY DATE(added)) AS q2 ON q1.day = q2.day 
ORDER BY 
    day

可惜我用的是Mysql5.7

这是期望的结果:

Date       | QSub | TopicSub
-----------+------+---------
2018-02-09 |   5  |   1
2018-02-19 |  19  |  13
2018-02-21 |  12  |   1
2018-02-22 |  43  |   0
2018-02-25 |   0  |   1

示例 user_submitted_q 数据:

+----+---------+-------------+--------+---------------------+----------------+
| id | user_id | question_id | result | date                | ip             |
+----+---------+-------------+--------+---------------------+----------------+
|  1 |       2 |         436 | good   | 2018-02-09 00:13:15 | 176.159.30.253 |
|  2 |       2 |         409 | good   | 2018-02-09 00:13:15 | 176.159.30.253 |
|  3 |       3 |         651 | wrong  | 2018-02-09 00:13:53 | 77.136.14.187  |
|  4 |       3 |         651 | wrong  | 2018-02-09 00:13:53 | 77.136.14.187  |
|  5 |       1 |          96 | wrong  | 2018-02-09 00:21:51 | 77.69.200.124  |
|  6 |       1 |          24 | good   | 2018-02-09 00:21:51 | 77.69.200.124  |
|  7 |       1 |          25 | good   | 2018-02-09 00:21:51 | 77.69.200.124  |
|  8 |       1 |          96 | wrong  | 2018-02-09 00:26:52 | 77.69.200.124  |
|  9 |       1 |          24 | good   | 2018-02-09 00:26:52 | 77.69.200.124  |
| 10 |       1 |          25 | good   | 2018-02-09 00:26:52 | 77.69.200.124  |
+----+---------+-------------+--------+---------------------+----------------+

示例 user_subscription 数据:

+----+---------+----------+---------------------+
| id | user_id | topic_id | added               |
+----+---------+----------+---------------------+
|  8 |       1 |       39 | 2018-02-09 00:27:30 |
|  9 |       4 |       47 | 2018-02-09 00:42:34 |
| 10 |       4 |       19 | 2018-02-09 00:42:34 |
| 11 |       5 |       47 | 2018-02-09 00:54:14 |
| 13 |       6 |       47 | 2018-02-09 01:00:23 |
| 14 |       6 |       19 | 2018-02-09 01:00:23 |
| 17 |       8 |       47 | 2018-02-09 01:06:50 |
| 18 |       8 |       19 | 2018-02-09 01:06:50 |
| 19 |       9 |       47 | 2018-02-09 01:08:33 |
| 20 |       9 |       19 | 2018-02-09 01:08:33 |
+----+---------+----------+---------------------+

预先感谢您的帮助

Answer 1

给定

MariaDB [sandbox]> select * from t order by dt;
+------------+
| dt         |
+------------+
| 2018-01-01 |
| 2018-01-02 |
| 2018-01-03 |
| 2018-01-03 |
| 2018-01-04 |
| 2018-01-04 |
+------------+
6 rows in set (0.00 sec)

MariaDB [sandbox]> select * from t1 order by dt;
+------------+
| dt         |
+------------+
| 2018-01-02 |
| 2018-01-03 |
| 2018-01-04 |
| 2018-01-05 |
| 2018-01-05 |
| 2018-01-06 |
| 2018-01-06 |
| 2018-01-07 |
| 2018-01-07 |
+------------+
9 rows in set (0.00 sec)

select tu.dt,(select count(*) from t where t.dt = tu.dt) tdt, 
    (Select count(*) from t1 where t1.dt = tu.dt) t1dt 
from
(
select dt from t
union
select dt from t1
) tu;

将建立跨越两个表的所有日期，然后子查询将对它们进行计数。

+------------+------+------+
| dt         | tdt  | t1dt |
+------------+------+------+
| 2018-01-01 |    1 |    0 |
| 2018-01-02 |    1 |    1 |
| 2018-01-03 |    2 |    1 |
| 2018-01-04 |    2 |    1 |
| 2018-01-05 |    0 |    2 |
| 2018-01-06 |    0 |    2 |
| 2018-01-07 |    0 |    2 |
+------------+------+------+
7 rows in set (0.00 sec)