mysql - 将两个聚合查询组合成一个 View

Question

我无法全神贯注于此。我有两个用于定期报告的查询:

mysql> select year(a.creation) as year, count(*) as 'built by year'
    -> from hosts a
    -> where year(a.creation) > 2013
    -> group by year(a.creation);
+------+---------------+
| year | built by year |
+------+---------------+
| 2014 |           372 |
| 2015 |          1115 |
| 2016 |           779 |
| 2017 |           268 |
+------+---------------+
4 rows in set (0.00 sec)

mysql> select year(b.decom) as year, count(*) as 'decomed by year'
    -> from hosts b
    -> where year(b.decom) > 2013
    -> group by year(b.decom);
+------+-----------------+
| year | decomed by year |
+------+-----------------+
| 2015 |              68 |
| 2016 |             816 |
| 2017 |              27 |
+------+-----------------+
3 rows in set (0.00 sec)

我希望能够在一个 View 中报告这两种情况。

我已经尝试了一些方法，但最接近的结果是一个很好的笛卡尔连接。幸运的是，数据库很小:

mysql> select year(a.creation) as year, count(a.hostname) as 'built by year', count(b.hostname) as 'decomed by year'
    -> from hosts a
    ->   left join hosts b on year(a.creation) = year(b.decom)
    -> where year(a.creation) > 2013 group by year(a.creation), year(b.decom);
+------+---------------+-----------------+
| year | built by year | decomed by year |
+------+---------------+-----------------+
| 2014 |           372 |               0 |
| 2015 |         75820 |           75820 |
| 2016 |        635664 |          635664 |
| 2017 |          7236 |            7236 |
+------+---------------+-----------------+
4 rows in set (3.59 sec)

Answer 1

再次感谢，我提出的查询如下所示:

select * from
(select year(a.creation) as year, count(*) as 'built by year'
from hosts a
where year(a.creation) > 2013
group by year(a.creation)
) x left join
( select year(decom) as year, count(b.hostname) as 'decomed by year'
from hosts b
where year(b.decom) > 2013
group by year(b.decom)
) y
on x.year = y.year