也许有人已经遇到过我现在遇到的同样的情况或问题。 我已经到处找了，但还是找不到解决这个问题的办法。 我有一个名为machines的数据库，其中有两个表:project和install。 表格如下:
Project table
+---------------+---------------------+
| project | date_imported |
+---------------+---------------------+
| Failed Add | 2020-06-15 07:28:51 |
| Failed Add | 2020-06-15 07:28:51 |
| Failed Add | 2020-06-15 07:28:51 |
| Failed Add | 2020-06-15 07:28:51 |
| Failed Remove | 2020-06-15 07:34:39 |
| Failed Remove | 2020-06-15 07:34:39 |
| Failed Remove | 2020-06-15 07:34:39 |
| Failed Remove | 2020-06-15 07:34:39 |
| Failed Remove | 2020-06-15 07:34:39 |
| Failed Add | 2020-06-16 01:22:16 |
| Failed Add | 2020-06-16 01:22:16 |
| Failed Add | 2020-06-16 01:22:16 |
| Failed Remove | 2020-06-16 13:17:32 |
| Failed Remove | 2020-06-16 13:17:32 |
+---------------+---------------------+
Install table
+---------------------+
| date_imported |
+---------------------+
| 2020-06-15 07:15:57 |
| 2020-06-15 07:15:57 |
| 2020-06-15 17:42:48 |
| 2020-06-15 17:42:48 |
| 2020-06-15 17:42:48 |
| 2020-06-15 17:42:48 |
| 2020-06-16 03:02:32 |
| 2020-06-16 03:02:32 |
| 2020-06-16 12:27:57 |
+---------------------+
我希望根据project表中的日期计算add和remove的数量，然后加入install表，该表也包含每个日期的总数。
以下是预期的输出:
+------------+------------+--------------+------------+
| Dates | Add | Remove | Install |
+------------+------------+--------------+------------+
| 2020-06-15 | 4 | 5 | 6 |
| 2020-06-16 | 3 | 2 | 3 |
+------------+------------+--------------+------------+
请告诉我怎样才能得到这个结果。
按日期在两个表中的每个表中聚合，然后模拟结果的完全外部连接:
with
cte_p as (
select date(date_imported) date,
sum(project = 'Failed Add') `Add`,
sum(project = 'Failed Remove') `Remove`
from Project
group by date
),
cte_i as (
select date(date_imported) date, count(*) Install
from Install
group by date
)
select p.date Dates,
p.`Add`, p.`Remove`, i.Install
from cte_p p left join cte_i i
on i.date = p.date
union
select i.date Dates,
p.`Add`, p.`Remove`, i.Install
from cte_i i left join cte_p p
on i.date = p.date
请参阅演示。
结果:
> Dates | Add | Remove | Install
> :--------- | --: | -----: | ------:
> 2020-06-15 | 4 | 5 | 6
> 2020-06-16 | 3 | 2 | 3
