如何改写SQL查询仅返回犯罪次数最多的COMMUNITY_AREA_NUMBER

原查询可以找出CHICAGO_CRIME_DATA表中出现次数最多的社区编号及其对应的犯罪记录数，但现在需要调整为只返回社区编号字段：

原查询语句：

select COMMUNITY_AREA_NUMBER, count(*) as CRIMES
from CHICAGO_CRIME_DATA
group by COMMUNITY_AREA_NUMBER
order by CRIMES desc
limit 1

你尝试的错误写法问题所在 #

你之前写的这个语句存在语法错误：

select COMMUNITY_AREA_NUMBER from CHICAGO_CRIME_DATA
where count(*) as CRIMES group by COMMUNITY_AREA_NUMBER order by CRIMES desc limit 1

WHERE子句中不能直接使用聚合函数count(*)，而且as CRIMES的位置也不符合SQL语法规范，聚合函数需要放在SELECT或者HAVING子句中，所以这个写法会直接报错。

可行的解决方法 #

方法1：子查询方式（你已经找到的解法）

把原查询作为子查询，外层只选取需要的字段即可，注意部分数据库要求子查询必须指定别名，所以可以给子查询加个简单别名（比如t）：

select COMMUNITY_AREA_NUMBER from
    (select COMMUNITY_AREA_NUMBER, count(*) as CRIMES
     from CHICAGO_CRIME_DATA
     group by COMMUNITY_AREA_NUMBER
     order by CRIMES desc
     limit 1) t

方法2：窗口函数（更灵活的写法）

如果存在多个社区的犯罪记录数并列最多的情况，limit 1只会返回其中一个，而用窗口函数可以返回所有符合条件的社区：

SELECT COMMUNITY_AREA_NUMBER
FROM (
    SELECT COMMUNITY_AREA_NUMBER,
           RANK() OVER(ORDER BY COUNT(*) DESC) AS rank_num
    FROM CHICAGO_CRIME_DATA
    GROUP BY COMMUNITY_AREA_NUMBER
) t
WHERE rank_num = 1;

这里用RANK()函数会保留并列的排名，如果想让并列名次不占用后续排名位置，可以改用DENSE_RANK()，根据实际需求选择即可。

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