如何编写SQL查询获取各地区频次最高的犯罪类型

阿华AIGC实验室

2026-5-15

我来帮你搞定这个需求！你的目标是找出每个地区里频次最高的犯罪类型，也就是把每个地区的犯罪类型按次数排序后，只保留排名第一的那条记录对吧？先给你分析下原SQL的问题：它确实能统计出每个地区所有犯罪类型的次数，但缺少了「筛选分组内最大值」的步骤。下面给你两种实用的解决方案：

解决方案：用窗口函数筛选分组内最高频次记录

我们可以借助窗口函数（ROW_NUMBER()或RANK()）来给每个地区的犯罪类型按频次排序，再筛选出排名第一的结果。

方法一：用`ROW_NUMBER()`（每个地区仅返回一条结果）

如果某个地区有多种犯罪类型频次相同且都是最高，这个方法会随机选一条（具体取决于数据库的默认排序规则）：

WITH CrimeCounts AS (
    SELECT 
        CrimeType.Category,
        District.Name AS DistrictName,
        COUNT(*) AS NoOfCrimes,
        -- 按地区分组，组内按犯罪次数降序排名
        ROW_NUMBER() OVER (PARTITION BY District.Name ORDER BY COUNT(*) DESC) AS Rank
    FROM Crime
    INNER JOIN CrimeType ON Crime.id_crime = CrimeType.id
    INNER JOIN District ON Crime.id_district = District.id
    GROUP BY CrimeType.Category, District.Name
)
SELECT Category, DistrictName, NoOfCrimes
FROM CrimeCounts
WHERE Rank = 1;

方法二：用`RANK()`（保留并列最高的结果）

如果需要保留某个地区里所有频次并列第一的犯罪类型，就用这个方法——它会让并列的记录排名都为1：

WITH CrimeCounts AS (
    SELECT 
        CrimeType.Category,
        District.Name AS DistrictName,
        COUNT(*) AS NoOfCrimes,
        -- 按地区分组，组内按犯罪次数降序排名，并列记录排名相同
        RANK() OVER (PARTITION BY District.Name ORDER BY COUNT(*) DESC) AS Rank
    FROM Crime
    INNER JOIN CrimeType ON Crime.id_crime = CrimeType.id
    INNER JOIN District ON Crime.id_district = District.id
    GROUP BY CrimeType.Category, District.Name
)
SELECT Category, DistrictName, NoOfCrimes
FROM CrimeCounts
WHERE Rank = 1;