SQL使用子查询查询犯罪数量最高的社区时出现错误如何解决

错误原因 #

你之前的代码报错是因为多数SQL方言不支持max(count(*))这种嵌套聚合函数的写法，聚合函数无法直接嵌套调用，必须先通过子查询得到每个社区的犯罪计数结果集，再对该结果集计算最大值。

正确实现代码 #

你可以用以下子查询写法实现需求，同时关联人口普查表获取社区名称：

%sql SELECT 
    c.COMMUNITY_AREA_NAME,
    cr.COMMUNITY_AREA_NUMBER,
    COUNT(cr.COMMUNITY_AREA_NUMBER) AS "Number of crimes in this community"
FROM CHICAGOCRIMEDATA cr
JOIN CHICAGOCENSUSDATA c ON cr.COMMUNITY_AREA_NUMBER = c.COMMUNITY_AREA_NUMBER
WHERE cr.COMMUNITY_AREA_NUMBER IS NOT NULL
GROUP BY cr.COMMUNITY_AREA_NUMBER, c.COMMUNITY_AREA_NAME
HAVING COUNT(cr.COMMUNITY_AREA_NUMBER) = (
    -- 子查询获取最高犯罪数量
    SELECT MAX(crime_count) 
    FROM (
        SELECT COUNT(*) AS crime_count 
        FROM CHICAGOCRIMEDATA 
        WHERE COMMUNITY_AREA_NUMBER IS NOT NULL
        GROUP BY COMMUNITY_AREA_NUMBER
    ) AS community_crime_counts
);

逻辑说明 #

• 最内层子查询先统计每个社区的犯罪总数，得到所有社区的犯罪计数结果集
• 外层子查询对该结果集取最大值，得到最高犯罪数量，替代原来的硬编码数值43
• 外层主查询筛选出犯罪数量等于该最大值的社区，同时关联人口普查表匹配对应的社区名称
• 该写法还可以处理多个社区犯罪数量并列第一的情况，会返回所有并列最高的社区，比LIMIT 1的兼容性更好

如果你的SQL运行环境支持公共表表达式（CTE），也可以用以下更易读的写法：

%sql WITH community_crime AS (
    SELECT 
        COMMUNITY_AREA_NUMBER,
        COUNT(*) AS crime_count
    FROM CHICAGOCRIMEDATA
    WHERE COMMUNITY_AREA_NUMBER IS NOT NULL
    GROUP BY COMMUNITY_AREA_NUMBER
)
SELECT 
    c.COMMUNITY_AREA_NAME,
    cc.COMMUNITY_AREA_NUMBER,
    cc.crime_count AS "Number of crimes in this community"
FROM community_crime cc
JOIN CHICAGOCENSUSDATA c ON cc.COMMUNITY_AREA_NUMBER = c.COMMUNITY_AREA_NUMBER
WHERE cc.crime_count = (SELECT MAX(crime_count) FROM community_crime);

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