# SQL 到聚合映射图表 在本页面 * [例子](#examples) [聚合管道](/aggregation/aggregation-pipeline.md)允许 MongoDB 提供与 SQL 中许多 common 数据聚合操作相对应的本机聚合功能。 以下 table 概述了 common SQL 聚合术语，函数和概念以及相应的 MongoDB [聚合运算符](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md)： | SQL 术语，函数和概念 | Mongo聚合命令 | | ---------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | WHERE | [$match](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | GROUP BY | [$group](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | HAVING | [$match](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | SELECT | [$project](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | ORDER BY | [$sort](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | LIMIT | [$limit](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | SUM() | [$sum](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | COUNT() | [$sum](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) [$sortByCount](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | join | [$lookup](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | SELECT INTO NEW\_TABLE | [$out](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) | | MERGE INTO TABLE | [$merge](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) （从MongoDB 4.2开始可用） | 有关所有聚合管道和表达式 operators 的列表，请参阅[聚合管道快速参考](/aggregation/aggregation-reference/aggregation-pipeline-quick-reference.md)。 > **也可以看看** > > SQL 到 MongoDB 映射图表]\(SQL-to-Aggregation-Mapping-Chart.md) ## 例子 以下 table 提供了 SQL 聚合 statements 和相应的 MongoDB statements 的快速 reference。 table 中的示例假定以下条件： * SQL 示例假设两个表`orders`和`order_lineitem`由`order_lineitem.order_id`和`orders.id`列连接。 * MongoDB 示例假设一个集合`orders`包含以下原型的文档： ``` { cust_id: "abc123", ord_date: ISODate("2012-11-02T17:04:11.102Z"), status: 'A', price: 50, items: [ { sku: "xxx", qty: 25, price: 1 }, { sku: "yyy", qty: 25, price: 1 } ] } ``` | SQL语句 | MongoDB语句 | 描述 | | ------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------- | | **SELECT** **COUNT**(\*) **AS** count **FROM** orders | db.orders.aggregate( \[ { $group: { \_id: null, count: { $sum: 1 } } } ] ) | 计算来自`orders`的所有记录 | | **SELECT** **SUM**(price) **AS** total **FROM** orders | db.orders.aggregate( \[ { $group: { \_id: null, total: { $sum: "$price" } } } ] ) | `orders`中对`price`字段求和 | | **SELECT** cust\_id, **SUM**(price) **AS** total **FROM** orders **GROUP BY** cust\_id | db.orders.aggregate( \[ { $group: { \_id: "$cust\_id", total: { $sum: "$price" } } } ] ) | 对于每个唯一`cust_id`，对`price`字段进行求和。 | | **SELECT** cust\_id, **SUM**(price) **AS** total **FROM** orders **GROUP BY** cust\_id **ORDER BY** total | db.orders.aggregate( \[ { $group: { \_id: "$cust\_id", total: { $sum: "$price" } } }, { $sort: { total: 1 } } ] ) | 对于每个唯一`cust_id`，求和`price`字段，结果按总和排序。 | | **SELECT** cust\_id, ord\_date, **SUM**(price) **AS** total **FROM** orders **GROUP BY** cust\_id, ord\_date | db.orders.aggregate( \[ { $group: { \_id: { cust\_id: "$cust\_id", ord\_date: { $dateToString: { format: "%Y-%m-%d", date: "$ord\_date" }} }, total: { $sum: "$price" } } } ] ) | 对于每个唯一的`cust_id`，通过`ord_date`分组，将`price`字段相加。排除 data 的 time 部分。 | | **SELECT** cust\_id, **count**(\*) **FROM** orders **GROUP BY** cust\_id **HAVING** **count**(\*) > 1 | db.orders.aggregate( \[ { $group: { \_id: "$cust\_id", count: { $sum: 1 } } }, { $match: { count: { $gt: 1 } } } ] ) | 对于具有多个记录的`cust_id`，返回`cust_id`和相应的 record 计数。 | | **SELECT** cust\_id, ord\_date, **SUM**(price) **AS** total **FROM** orders **GROUP BY** cust\_id, ord\_date **HAVING** total > 250 | db.orders.aggregate( \[ { $group: { \_id: { cust\_id: "$cust\_id", ord\_date: { $dateToString: { format: "%Y-%m-%d", date: "$ord\_date" }} }, total: { $sum: "$price" } } }, { $match: { total: { $gt: 250 } } } ] ) | 对于每个唯一的`cust_id`，通过`ord_date`分组，仅在总和大于 250 的情况下对`price`字段和 return 求和。排除 date 的 time 部分 | | **SELECT** cust\_id, SUM(price) **as** total **FROM** orders **WHERE** status = 'A' **GROUP BY** cust\_id | db.orders.aggregate( \[ { $match: { status: 'A' } }, { $group: { \_id: "$cust\_id", total: { $sum: "$price" } } } ] ) | 对于状态为`A`的每个唯一`cust_id`，请对`price`字段求和。 | | **SELECT** cust\_id, **SUM**(price) **as** total **FROM** orders **WHERE** status = 'A' **GROUP BY** cust\_id **HAVING** total > 250 | db.orders.aggregate( \[ { $match: { status: 'A' } }, { $group: { \_id: "$cust\_id", total: { $sum: "$price" } } }, { $match: { total: { $gt: 250 } } } ] ) | 对于状态为`A`的每个唯一`cust_id`，仅对总和大于 250 的`price`字段和 return 求和。 | | **SELECT** cust\_id, **SUM**(li.qty) **as** qty **FROM** orders o, order\_lineitem li **WHERE** li.order\_id = o.id **GROUP BY** cust\_id | db.orders.aggregate( \[ { $unwind: "$items" }, { $group: { \_id: "$cust\_id", qty: { $sum: "$items.qty" } } } ] ) | 对于每个唯一`cust_id`，将与订单关联的相应 line item `qty`字段相加。 | | **SELECT** **COUNT**(\*) **FROM** (**SELECT** cust\_id, ord\_date **FROM** orders **GROUP** **BY** cust\_id, ord\_date) **as** DerivedTable | db.orders.aggregate( \[ { $group: { \_id: { cust\_id: "$cust\_id", ord\_date: { $dateToString: { format: "%Y-%m-%d", date: "$ord\_date" }} } } }, { $group: { \_id: **null**, count: { $sum: 1 } } } ] ) | 计算不同的`cust_id`，`ord_date`分组的数量。排除 date 的 time 部分。 | > **也可以看看** * [SQL 到 MongoDB 映射图表](/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md) * [聚合管道快速参考](/aggregation/aggregation-reference/aggregation-pipeline-quick-reference.md) * [db.collection.aggregate()](/can-kao/mongo-shell-methods/collection-methods/db-collection-aggregate.md) 译者：李冠飞 校对： --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.mongoing.com/aggregation/aggregation-reference/sql-to-aggregation-mapping-chart.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.