mysql - 计算每个地方的用户数

Question

我有一个表位置，它将存储有关位置的详细信息，例如

Place(id,name,detail,type,Pid);//pid为父地点id

用户(id,姓名,详细信息);

user_place(uid,pid);//用户和地点表主键

这里的地点可以是地区、城市、州或国家。

我想跟踪特定地点的用户数量

就像区域一样，我想获取该区域的总用户，即 400

再次对于城市，我想获取总用户(即城市所有区域的总用户)，即 50000(对于城市)。

州和国家/地区类似。

计算以上数据，表结构是否正确？或者需要任何修改吗？如何根据城市、州、国家计算用户数量？

Answer 1

假设:用户位于区域。 地区位于城市中，城市位于国家中。

阿卡姆地区的用户总数:

select count(up.uid)
  from place p
    inner join user_place up
      on p.id = up.pid
  where p.type = 'Area' and p.name = 'Arkham';

拥有多个区域的 gotham 城市的用户总数。

select count(up.uid)
  from place p
    inner join place p2
      on p.pid = p2.id and p.type = 'Area' and p2.type = 'City'
    inner join user_place up
      on p.id = up.pid
    where p2.name = 'gotham'

这里有一个演示:http://sqlfiddle.com/#!9/14238/5