请求帮助：将指定SQL查询转换为MongoDB聚合查询

首先，先拆解你的原SQL逻辑：它要做的是按年份和犯罪小类别分组统计数量，然后筛选出每个年份中出现次数最多（如果有并列则取小类别名称排序最靠前的）的那个小类别的统计结果。你的聚合代码目前卡在了如何实现SQL中HAVING子句里的子查询逻辑，这在MongoDB里不能直接照搬SQL的写法，我们需要用聚合管道的多阶段来实现，下面是具体的解决方案：

完整的MongoDB聚合查询（MongoDB 5.0+ 推荐） #

db.crimes.aggregate([
  // 第一步：按年份和小类别分组，统计每个组合的犯罪数量
  {
    $group: {
      _id: {
        year: "$year",
        minor_category: "$minor_category"
      },
      count: { $sum: 1 } // 注意：统计文档数量要用$sum:1，而非sum字段值
    }
  },
  // 第二步：用窗口函数给每个年份内的分组按规则排名
  {
    $setWindowFields: {
      partitionBy: "$_id.year", // 按年份分区，对应SQL子查询的"cc.year=c.year"
      sortBy: {
        count: -1, // 先按统计数量降序
        "_id.minor_category": 1 // 数量相同时，按小类别名称升序取第一个
      },
      output: {
        rank: { $rank: {} } // 给每个分区内的文档分配排名
      }
    }
  },
  // 第三步：筛选出每个年份排名第一的分组，对应SQL的HAVING子句逻辑
  {
    $match: { rank: 1 }
  },
  // 可选：整理输出字段，让结果和SQL查询格式一致
  {
    $project: {
      _id: 0,
      year: "$_id.year",
      minor_category: "$_id.minor_category",
      count: 1
    }
  }
])

关键步骤解释 #

1. $group阶段：对应SQL的GROUP BY c.year, c.minor_category，我们统计每个(year, minor_category)组合的犯罪记录数。你原来的代码里$sum: "$minor_category"是错误的——这是对minor_category字段的字符串值求和，而我们需要的是统计该分组的文档数量，所以应该用$sum: 1。

2. $setWindowFields阶段：这是实现原SQL子查询逻辑的核心。原SQL的子查询是对每个年份，找出count最多且minor_category排序最靠前的类别，这里我们用窗口函数按年份分区，然后按count降序、minor_category升序排序，给每个分组分配排名。排名为1的就是我们要找的目标分组。

3. $match阶段：筛选出排名为1的文档，完全等价于SQL里HAVING子句的筛选逻辑。

4. $project阶段：可选步骤，把输出字段整理成和SQL查询结果一致的格式，去掉自动生成的_id，把_id里的year和minor_category单独提取出来。

兼容旧版本MongoDB（5.0以下）的方案 #

如果你使用的MongoDB版本不支持窗口函数，可以用以下方式实现相同逻辑：

db.crimes.aggregate([
  // 第一步：先分组统计每个(year, minor_category)的数量
  {
    $group: {
      _id: { year: "$year", minor_category: "$minor_category" },
      count: { $sum: 1 }
    }
  },
  // 第二步：按年份分组，把该年份下的所有分组结果存入数组
  {
    $group: {
      _id: "$_id.year",
      categories: {
        $push: {
          minor_category: "$_id.minor_category",
          count: "$count"
        }
      }
    }
  },
  // 第三步：对每个年份的categories数组按规则排序，取第一个元素
  {
    $project: {
      _id: 0,
      year: "$_id",
      top_category: {
        $arrayElemAt: [
          {
            $sortArray: {
              input: "$categories",
              sortBy: { count: -1, minor_category: 1 }
            }
          },
          0
        ]
      }
    }
  },
  // 第四步：展开top_category，整理成最终输出格式
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          { year: "$year" },
          "$top_category"
        ]
      }
    }
  }
])

这个方案通过先按年份聚合所有分组结果到数组，再排序数组并取第一个元素，达到和窗口函数一致的效果。

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