查询Locations表中拥有最多已开启地点的PlayerID并修正错误SQL

原语句错误点梳理 #

• 聚合函数COUNT()不允许直接写在WHERE子句中：WHERE的执行时机早于分组聚合，只能过滤原始表行，无法对聚合结果做判断
• 排序关键字使用错误，标准SQL排序使用ORDER BY，不存在SORT BY关键字
• 缺少分组逻辑：要统计每个PlayerID对应的已开启地点数量，必须先按PlayerID分组，才能得到单用户的统计结果

修正后语句 #

支持TOP语法的数据库（SQL Server、Access等） #

SELECT TOP 1 PlayerID
FROM Locations
GROUP BY PlayerID
ORDER BY COUNT(openedLocations) DESC

支持LIMIT语法的数据库（MySQL、PostgreSQL、SQLite等） #

SELECT PlayerID
FROM Locations
GROUP BY PlayerID
ORDER BY COUNT(openedLocations) DESC
LIMIT 1

并列最大值兼容方案 #

如果存在多个玩家开启地点数量并列最多的情况，上述写法只会返回第一个匹配的ID，如需返回所有并列第一的PlayerID，可以使用通用写法：

SELECT PlayerID
FROM Locations
GROUP BY PlayerID
HAVING COUNT(openedLocations) = (
    SELECT MAX(cnt) 
    FROM (
        SELECT COUNT(openedLocations) cnt 
        FROM Locations 
        GROUP BY PlayerID
    ) t
)

内容的提问来源于stack exchange，提问作者miki95