代码之家 › 专栏 › 技术社区 › shamsieh76

五个Mysql表格中每个国家、州、市和地区的销售额

mysqli sql mysql php

-2

shamsieh76 · 技术社区 · 6 年前

我有下面五个表,如果可能的话,我想使用一个mysql查询来显示每个国家、州、城市和地区的销售总额:

+------------+----------+
|  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     |
+----------+------------+----------+---------+-------------+--------+

Demo with my current attempt

谢谢你

1 回复 | 直到 6 年前

MichaÅ Turczyn 6 年前

如果你想按三列进行分组( city_id , state_id district_id ),这是毫无意义的,因为它不会改变你的生活 sales 桌子。你已经有了部分信息。

此外,在表中有0代替了某些ID,并且在任何表中都没有任何ID等于0。我想你需要重新考虑你真正想要什么。

SELECT country,
       state,
       city,
       district,
       coalesce(amount, 0) amount
FROM country ctr
JOIN state_province st ON ctr.country_id = st.country_id
JOIN city c ON c.state_id = st.state_id
LEFT JOIN district d ON d.city_id = c.city_id
LEFT JOIN sales s ON
    s.country_id = ctr.country_id AND
    s.state_id = st.state_id AND
    s.city_id = c.city_id AND
    s.district_id = d.district_id

Demo