mysql - 在单个mysql查询中查找计数和数据

Question

我有两张 table 。城市、地区。

Cities:  
    id | name  
Localities:  
    id | city_id | name

我可以通过一次查询来获取城市的地点列表(每个城市仅 10 行)以及该城市的地点数量吗？

我知道我可以通过两个不同的查询来做到这一点。

请建议是否可以使用 Django 查询集来完成。

型号:

class City(models.Model):
    name = models.CharField(max_length=200)    

class Locality(models.Model):
    name = models.CharField(max_length=200)
    city = models.ForeignKey(City,related_name='LocalityCity')

Answer 1

select * from
( select c.id as city_id, count(l.id) as "localities count per city"
  from cities as c
  inner join localities as l on l.city_id = c.id ) as localities_count_per_city
inner join
( select c.id as city_id, c.name, l.name
  from cities as c
  inner join localities as l on l.city_id = c.id
  limit 10) as localities_per_city ) as localities_per_city
on localities_count_per_city.city_id = localities_per_city.city_id

这应该将每个城市的相应地区计数与每个地区的城市行连接起来。