我有下面五个表,如果可能的话,我想使用一个mysql查询来显示每个国家、州、城市和地区的销售总额:
注意:如果州、市、区没有销售,那么我需要查询结果显示0或空格(即使特定州、市、区没有销售,也必须显示州、市、区名称)
+------------+----------+
| country |
+------------+----------+
| country_id | country |
+------------+----------+
| 1 | country1 |
| 2 | country2 |
+------------+----------+
+----------------+--------+------------+
| state_province |
+----------------+--------+------------+
| state_id | state | country_id |
+----------------+--------+------------+
| 1 | state1 | 1 |
| 2 | state1 | 2 |
| 3 | state2 | 2 |
| 4 | state2 | 1 |
+----------------+--------+------------+
+---------+-------+----------+
| city |
+---------+-------+----------+
| city_id | city | state_id |
+---------+-------+----------+
| 1 | city1 | 1 |
| 2 | city2 | 1 |
| 3 | city1 | 3 |
| 4 | city2 | 3 |
| 5 | city1 | 4 |
| 6 | city2 | 4 |
+---------+-------+----------+
请注意,country(country\u id=2)中的state(state\u id=2)在上表中没有城市。
+-------------+-----------+---------+
| district |
+-------------+-----------+---------+
| district_id | district | city_id |
+-------------+-----------+---------+
| 1 | district1 | 1 |
| 2 | district2 | 1 |
| 3 | district1 | 2 |
| 4 | district2 | 2 |
| 5 | district1 | 4 |
| 6 | district2 | 4 |
| 7 | district1 | 5 |
| 8 | district1 | 6 |
+-------------+-----------+---------+
+----------+------------+----------+---------+-------------+--------+
| sales |
+----------+------------+----------+---------+-------------+--------+
| sales_id | country_id | state_id | city_id | district_id | amount |
+----------+------------+----------+---------+-------------+--------+
| 1 | 1 | 0 | 0 | 0 | 1000 |
| 2 | 1 | 0 | 0 | 0 | 2000 |
| 3 | 1 | 1 | 0 | 0 | 300 |
| 4 | 1 | 1 | 0 | 0 | 70 |
| 5 | 1 | 1 | 1 | 0 | 50 |
| 6 | 1 | 1 | 1 | 1 | 25 |
| 7 | 1 | 4 | 1 | 1 | 25 |
| 8 | 1 | 4 | 5 | 0 | 25 |
| 9 | 2 | 0 | 0 | 0 | 3000 |
| 10 | 2 | 0 | 0 | 0 | 500 |
| 11 | 2 | 3 | 0 | 0 | 300 |
| 12 | 2 | 3 | 4 | 6 | 70 |
+----------+------------+----------+---------+-------------+--------+
演示我当前的尝试
谢谢您
1条答案
按热度按时间ubbxdtey1#
如果你想按三列进行分组(
city_id
,state_id
,district_id
),这是毫无意义的,因为它不会改变你的生活sales
table。你已经有了部分信息。此外,在
sales
表中有0代替了某些id,并且在任何表中都没有任何id等于0。我想你需要重新考虑你真正想要什么。我想你只需要简单的加入:
演示