3.4. SQL 和 MongoDB 聚合对比_MongoDB 应用指南

MongoDB pipeline 提供了许多等价于 SQL 中常见聚合语句的操作。

下表概述了常见的 SQL 聚合语句或函数和 MongoDB 聚合操作的映射表：

SQL Terms, Functions, and Concepts	MongoDB Aggregation Operators
`WHERE`	`$match`(opens new window)
`GROUP BY`	`$group`(opens new window)
`HAVING`	`$match`(opens new window)
`SELECT`	`$project`(opens new window)
`ORDER BY`	`$sort`(opens new window)
`LIMIT`	`$limit`(opens new window)
`SUM()`	`$sum`(opens new window)
`COUNT()`	`$sum` (opens new window)`$sortByCount`(opens new window)
`JOIN`	`$lookup`(opens new window)
`SELECT INTO NEW_TABLE`	`$out`(opens new window)
`MERGE INTO TABLE`	`$merge` (opens new window)(Available starting in MongoDB 4.2)
`UNION ALL`	`$unionWith` (opens new window)(Available starting in MongoDB 4.4)

![](http://img.jiangguo.net/266e7e3141736d56)db.orders.insertMany([
  {
    _id: 1,
    cust_id: 'Ant O. Knee',
    ord_date: new Date('2020-03-01'),
    price: 25,
    items: [
      { sku: 'oranges', qty: 5, price: 2.5 },
      { sku: 'apples', qty: 5, price: 2.5 },
    ],
    status: 'A',
  },
  {
    _id: 2,
    cust_id: 'Ant O. Knee',
    ord_date: new Date('2020-03-08'),
    price: 70,
    items: [
      { sku: 'oranges', qty: 8, price: 2.5 },
      { sku: 'chocolates', qty: 5, price: 10 },
    ],
    status: 'A',
  },
  {
    _id: 3,
    cust_id: 'Busby Bee',
    ord_date: new Date('2020-03-08'),
    price: 50,
    items: [
      { sku: 'oranges', qty: 10, price: 2.5 },
      { sku: 'pears', qty: 10, price: 2.5 },
    ],
    status: 'A',
  },
  {
    _id: 4,
    cust_id: 'Busby Bee',
    ord_date: new Date('2020-03-18'),
    price: 25,
    items: [{ sku: 'oranges', qty: 10, price: 2.5 }],
    status: 'A',
  },
  {
    _id: 5,
    cust_id: 'Busby Bee',
    ord_date: new Date('2020-03-19'),
    price: 50,
    items: [{ sku: 'chocolates', qty: 5, price: 10 }],
    status: 'A',
  },
  {
    _id: 6,
    cust_id: 'Cam Elot',
    ord_date: new Date('2020-03-19'),
    price: 35,
    items: [
      { sku: 'carrots', qty: 10, price: 1.0 },
      { sku: 'apples', qty: 10, price: 2.5 },
    ],
    status: 'A',
  },
  {
    _id: 7,
    cust_id: 'Cam Elot',
    ord_date: new Date('2020-03-20'),
    price: 25,
    items: [{ sku: 'oranges', qty: 10, price: 2.5 }],
    status: 'A',
  },
  {
    _id: 8,
    cust_id: 'Don Quis',
    ord_date: new Date('2020-03-20'),
    price: 75,
    items: [
      { sku: 'chocolates', qty: 5, price: 10 },
      { sku: 'apples', qty: 10, price: 2.5 },
    ],
    status: 'A',
  },
  {
    _id: 9,
    cust_id: 'Don Quis',
    ord_date: new Date('2020-03-20'),
    price: 55,
    items: [
      { sku: 'carrots', qty: 5, price: 1.0 },
      { sku: 'apples', qty: 10, price: 2.5 },
      { sku: 'oranges', qty: 10, price: 2.5 },
    ],
    status: 'A',
  },
  {
    _id: 10,
    cust_id: 'Don Quis',
    ord_date: new Date('2020-03-23'),
    price: 25,
    items: [{ sku: 'oranges', qty: 10, price: 2.5 }],
    status: 'A',
  },
])

SQL 和 MongoDB 聚合方式对比

下一节：参考：https://docs.mongodb.com/guides/server/introduction/#what-you-ll-need