mysql - mysql中的SUM和Group by逻辑问题

Question

我有三张 table

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)，则查找每个城市的商店总数 AS OTHERS

我尝试过，但无法进一步

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

Answer 1

每个城市的商店数量:

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