sql - 共享层次结构复制和 LEFT JOIN

Question

假设我只能在 SQLite 中执行此操作。

我有两张 table 。

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

State 表中有明显的层次结构。一个州有若干个区，每个区有若干个分区，每个分区有若干个市。

另一张表是一个城市的学生人数，其中 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 区所有城市的学生总数。

这就是并发症。

一些 城市在分区之间共享 .在这种情况下，一个区有一个跨两个分区共享的城市。这反射(reflect)在状态表中。

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() 值加倍，因为同一个城市被计算为 TWICE。

我如何通过不更改表的模式来解决由于这个技术上错误但现实的问题而在逻辑上出现的复杂重复？我尝试使用 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