时间戳

首页 > 解决方案 > 祖父母、父母、子女等的案件总和

php - 祖父母、父母、子女等的案件总和

问题描述

我需要的是计算以下情况的总数：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= 新的 covid-19 病例；r_cases= 康复病例；d_cases= 死亡病例

到这一点：

  1. 我可以使用这样的查询获得每个城市的病例总数（例如新病例）：

    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 = '#'
    1. 我能够获得所有城市的所有病例（例如新病例）的总和：
选择
sum(`n_cases`) AS total_n_cases,
从
covid19城市

现在，我需要计算以下案件的总数：

那么，我该如何实现呢？我想到的是

  1. 查找所有区域
  2. 在 fetch assoc while-loop 中，我搜索该地区的地区
  3. 在我搜索父城市的地区的 fetch assoc while 循环中
  4. 在父城市的 fetch assoc while 循环中，我搜索子城市
  5. 计算总和，然后向后添加到父城市，然后从那里到地区，然后到地区！

但我相信这不是应该做的。但是，在这种情况下，我不知道如何继续跟踪亲子城市。

感谢您的建议和帮助。

谢谢

p.s. sorry for my English :/

标签： phpmysqlsqldatabasejoin

解决方案

考虑以下基本查询，它为您提供每个案例类别的总和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

从那里开始，我们可以通过在表上添加更多连接并更改and子句cities中的列来逐级向上跟踪层次结构。selectgroup 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

这是相同数量的连接，但事物存储在不同的表中，因此查询更易于编写和阅读。

推荐阅读