mysql合并两个表的数据

Question

不确定这在数据库端是否可行，但到目前为止我只能得到这个结果:

查询:

SELECT City.city_name "City", PC.subcategory_id "Subcategory", PC.count "count" FROM products_counter PC , Cities City WHERE PC.city_id = City.city_id

+-----------+----------------+-------+
| city_name | subcategory_id | count |
+----------------------------+-------+
| City1     | fruits         | 4     |
| City2     | vegetables     | 4     |
| City1     | meat           | 1     |
+-----------+----------------+-------+

这是我的两个表:

表products_counter:

+-------+---------+----------------+-------+
| ID    | city_id | subcategory_id | count |
+-------+---------+----------------+-------+
|     1 |       1 | fruits         |     4 |
|     2 |       2 | vegetables     |     4 |
|     3 |       2 | meat           |     1 |
+-------+---------+----------------+-------+

表格城市:

+---------+------------+
| city_id | city_name  |
+---------+------------+
|       1 | City1      |
|       2 | City2      |
|       3 | City3      |
+---------+------------+

这是预期的结果:

+-----------+----------------+-------+
| city_name | subcategory_id | count |
+-----------+----------------+-------+
| City1     | fruits         | 4     |
| City1     | vegetables     | 0     | 
| City1     | meat           | 0     |
| City2     | fruits         | 0     |
| City2     | vegetables     | 4     |
| City1     | meat           | 1     |
| City3     | fruits         | 0     |
| City3     | vegetables     | 0     |
| City3     | meat           | 0     |
+-----------+----------------+-------+

但我不太确定如何列出 Cities 表中的所有城市，然后在 city_id 和 subcategory_id 相等的情况下分配 count 列。

Answer 1

您可以为此使用交叉连接。

SELECT c.city_name, pc.subcategory_id,
IFNULL((select `count` from products_counter where city_id = c.city_id 
                and subcategory_id = pc.subcategory_id),0) as 'Count'
FROM cities c CROSS JOIN products_counter pc

这里的工作示例 - http://sqlfiddle.com/#!9/34c38/16