mysql - SUM和MySQL中的逻辑分组

我有三张桌子

City(Id,CityName)
Malls(Id,MallName,CityId)
Stores(Id,StoreName,MallId)

我想要的是：

1 =我想找到每个城市旁边所有商店的总和

2 =如果CityId IN（1,2,3,5）查找每个城市的商店总和

3 =如果CityId IN（4,6,7,8），则查找每个城市中其他商店的总和

我尝试了这个，但无法走得更远

select count(id) FROM Stores AS s ,Malls AS m,City AS c where s.mallid=m.id and m.cityid=c.id GROUP BY c.id

最佳答案

每个城市的商店数量：

SELECT c.CityName, COUNT(c.id) AS StoresInCity
FROM Stores s
INNER JOIN Malls m ON s.mallid = m.id
INNER JOIN City c ON m.cityid = c.id
GROUP BY c.CityName

在每个城市中CityId IN(1, 2, 3, 5)的商店数量：

SELECT c.CityName, COUNT(c.id) AS StoresInCity
FROM Stores s
INNER JOIN Malls m ON s.mallid = m.id
INNER JOIN City c ON m.cityid = c.id
WHERE m.CityId IN (1, 2, 3, 5)
GROUP BY c.CityName

在每个城市中CityId IN(4, 6, 7, 8)的商店数量：

SELECT c.CityName, COUNT(c.id) AS StoresInCity
FROM Stores s
INNER JOIN Malls m ON s.mallid = m.id
INNER JOIN City c ON m.cityid = c.id
WHERE m.CityId IN (4, 6, 7, 8)
GROUP BY c.CityName