mysql - MySQL，如何在不同的表上连接和分组两列

我在mysql中有两个表：

table name = 'static':
+----+----------+-----------+
| id | username | ip        |
+----+----------+-----------+
| 1  | foo      | 172.0.0.1 |
+----+----------+-----------+
| 2  | bar      | 172.0.0.2 |
+----+----------+-----------+
| 3  | baz      | 172.0.0.3 |
+----+----------+-----------+

table name = 'dynamic':
+----+----------+-----------+
| id | username | ip        |
+----+----------+-----------+
| 1  | bill     | 172.0.0.1 |
+----+----------+-----------+
| 2  | ben      | 172.0.0.4 |
+----+----------+-----------+
| 3  | hen      | 172.0.0.5 |
+----+----------+-----------+

我想要的是将两个表中的“ ip”列合并在一起，然后按ip分组，然后返回被分组的相同ip的数量。

结果示例：

+-----------+----------+
| ip        | quantity |
+-----------+----------+
| 172.0.0.1 | 2        |
+-----------+----------+
| 172.0.0.2 | 1        |
+-----------+----------+
| 172.0.0.3 | 1        |
+-----------+----------+
| 172.0.0.4 | 1        |
+-----------+----------+
| 172.0.0.5 | 1        |
+-----------+----------+

最佳答案

您不需要联接，但需要UNION ALL，然后进行分组：

select t.ip, count(*) quantity from (
  select ip from static
  union all
  select ip from dynamic
) t
group by t.ip

关于mysql - MySQL，如何在不同的表上连接和分组两列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56959831/