我需要的是计算以下上的病例总数:parent cities
、districts
和regions
所以让我告诉你目前的情况以及我到目前为止做了什么
我有两个表[cities]
&[covid19cities]
[城市]表: 参考表
结构为:
------------------------------
| id | parent_id | city_name |
------------------------------
城市级别为:
- Region //[its parent_id = 0]
-- District //[its parent_id = the region id]
---- Parent-city //[its parent_id = the district id]
------ Child-city //[its parent_id = the parent-city id]
[covid19cities]表:
结构为:
-----------------------------------------------------
| id | city_id | date | n_cases | r_cases | d_cases |
-----------------------------------------------------
因此,我们每天都在[covid19cities]
中填写不同城市的不同病例:
n_cases
=新增新冠肺炎病例;r_cases
=康复病例;d_cases
=死亡病例
到此为止:
我可以使用以下查询获得每个城市的病例总数(例如新病例(:
SELECT sum(`n_cases`) AS city_n_cases, cities.name AS city_name, cities.id AS city_id, FROM covid19cities INNER JOIN cities ON cities.id = covid19cities.city_id WHERE covid19cities.city_id = '#'
- 我能够获得所有城市的所有病例(例如新病例(的总和:
SELECTsum(`n_cases`(AS total_n_cases,来自Covid19城市
现在,我需要的是在上统计病例总数
- 母城市
- 地区
- 区域
那么,我该如何做到这一点呢?我想的是
- 查找所有区域
- 在获取assoc while循环中,我搜索该区域的区域
- 在地区的fetch assoc while循环中,我搜索母城市
- 在父城市的fetch assoc while循环中,我搜索子城市
- 计算总数,然后加回到母城市,从那里到地区,再到地区
但我认为这不是应该做的。然而,在这种情况下,我不知道如何继续追踪父母子女所在的城市。
我感谢你的建议和帮助。
感谢
p.s. sorry for my English :/
考虑以下基本查询,它为您提供每个city_id
的每个案例类别的总和。我们可以通过只查看covid19cities
来获得这些信息:
select
cvc.city_id,
sum(cvc.n_cases) sum_n_cases,
sum(cvc.r_cases) sum_r_cases,
sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
group by cvc.city_id
现在让我们来看看cities
表格。这会得到相同的结果,并且您也可以显示城市名称:
select
c.id city_id,
c.name city_name,
sum(cvc.n_cases) sum_n_cases,
sum(cvc.r_cases) sum_r_cases,
sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c on c.id = cvc.city_id
group by c.id, c.name
从那里开始,我们可以通过在cities
表上添加更多联接并更改select
和group by
子句中的列,逐级向上遵循层次结构。
让我们获得每个父城市的病例数:我们第二次加入cities
表,别名pc
(用于父城市(:
select
pc.id parent_city_id,
pc.name parent_city_name,
sum(cvc.n_cases) sum_n_cases,
sum(cvc.r_cases) sum_r_cases,
sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c on c.id = cvc.city_id
inner join cities pc on pc.id = c.parent_id
group by pc.id, pc.name
下一个级别是区域:
select
d.id distict_id,
d.name district_name,
sum(cvc.n_cases) sum_n_cases,
sum(cvc.r_cases) sum_r_cases,
sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c on c.id = cvc.city_id
inner join cities pc on pc.id = c.parent_id
inner join cities d on d.id = pc.parent_id
group by d.id, d.name
最后,这里是给出上层信息的查询,即区域:
select
r.id region_id,
r.name region_name,
sum(cvc.n_cases) sum_n_cases,
sum(cvc.r_cases) sum_r_cases,
sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c on c.id = cvc.city_id
inner join cities pc on pc.id = c.parent_id
inner join cities d on d.id = pc.parent_id
inner join cities r on r.id = d.parent_id
group by r.id, r.name
作为一个更普遍的想法:请注意,这里的复杂性来自于您将层次结构存储在cities
表中的事实。使用单独的表来存储每个实体会简单得多,并使用外键来表示关系,例如:
regions: region_id, region_name
districts: district_id, district_name, region_id
parent_cities: parent_city_id, parent_city_name, district_id
cities: city_id, city_name, parent_city_id
使用这种设计,您的最后一个查询看起来像:
select
r.region_id,
r.region_name,
sum(cvc.n_cases) sum_n_cases,
sum(cvc.r_cases) sum_r_cases,
sum(cvc.d_cases) sum_d_cases
from covid19cities cvc
inner join cities c on c.id = cvc.city_id
inner join parent_cities pc on pc.parent_city_id = c.parent_city_id
inner join districts d on d.district_id = pc.district_id
inner join regions r on r.region_id = d.region_id
group by r.region_id, r.region_name
这是相同数量的联接,但内容存储在不同的表中,因此查询更易于编写和读取。
由于只有3个级别,而不是任意数量的级别,我建议您过于努力。有一个单独的表,表中列出了地区、地区和城市的3列。这只是每天几千行,所以缺乏规范化不会导致巨大的磁盘开销。
另一方面,如果这是一个学习练习,那么获取MySQL 8或MariaDB 10.2并学习"递归CTE"。