我有两张桌子:景点和城市。景点包含一个名为city的列,它是对cities表中id的引用。我想形成一个MySQL语句,告诉我哪些城市最吸引人。
我知道我能做到:

SELECT COUNT(*) as `number`
 FROM `attractions`
WHERE `city` = XX

…要计算一个城市有多少个景点,但是否有一个语句可以计算每个城市的所有景点,并按景点数量降序返回结果?

最佳答案

SELECT City.Name, COUNT(Attractions.City) as AttractionCount
FROM City
LEFT OUTER JOIN Attractions ON City.City = Attractions.City
GROUP BY City.Name
ORDER BY COUNT(Attractions.City) DESC

关于sql - 帮助形成SQL语句,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/3384577/

10-16 14:55