共享层次结构复制和左联接

Question

让我们假设我只能在SQLite中这样做。

我有两张桌子。

State {
  DistrictID: int
  SubdistrictID: int
  CityID: Int
  name: varchar(36)
}

国庆桌上有一个明显的王权。一个州有若干个区，每个区有若干个分区，每个分区有若干个城市。

另一个表是城市中的学生人数，其中Students.CityID = State.CityID

Students {
  CityID: int
  number: int
}

我想知道一个地区“221”的学生人数。

查询：SELECT sum(students.number) from State LEFT JOIN Students students ON State.CityID = students.CityID WHERE State.DistrictID = 221 GROUP BY State.DistrictID

到目前为止，这确实有效。我得到来自221区所有城市的学生总数。

这是并发症。

一些城市在分区之间共享。在这种情况下，一个地区有一个城市，在两个分区共享。这反映在国家表内。

State Table
Row0: DistrictID: 221; SubDistrictID: 332; CityID: 554
Row1: DistrictID: 221; SubDistrictID: 332; CityID: 555
Row2: DistrictID: 221; SubDistrictID: 333; CityID: 554
Row3: DistrictID: 221; SubDistrictID: 333; CityID: 557

第0行和第2行的城市(554)共享在两个分区- 332和333。

在这种情况下，上面的sql查询将使SUM()值加倍，因为同一个城市被计算了两次。

我应该如何通过不改变表的模式来解决由于这个技术错误但现实问题而在逻辑上产生的复杂的重复呢？我试过使用distinct，但它不符合这个目的，因此，不起作用。

Answer 1

在执行select distinct之前，您可以使用join获取该地区每个城市的一个引用。

select sum(s.number)
from (select distinct cityid
      from state
      where destrictid = 21
     ) c left join
     students s
     on s.cityid = c.cityid

Answer 2

Sum将添加所有的Student.Numbers。如果你想要学生人数，你应该使用count。为了给你一个想法，如果那个地区有两个学生，一个是Student.number 1，另一个是Student.Number 4，sum将返回5，count返回2：

select sum(S.number) 
from Students S inner join State St on S.CityId = St.CityId 
where St.DistrictID = 221 

因为您正在寻找单个DistrictId，所以不需要group by districtId。另外，我使用的是inner join，因为您只想让那些在State表中使用CityId的学生。

编辑：

您可以按以下方式加入其他表：

select D.DistrictName, sum(S.number) 
from Students S inner join State St on S.CityId = St.CityId 
inner join City C on St.CityId = C.Id
inner join District D on St.DistrictId = D.DistrictId
where St.DistrictID = 221 
group by D.DistrictName