mongoDB与sql聚合操作对应图

341243

SQL Terms, Functions, and Concepts	MongoDB Aggregation Operators
WHERE	$match
GROUP BY	$group
HAVING	$match
SELECT	$project
ORDER BY	$sort
LIMIT	$limit
SUM()	$sum
COUNT()	$sum
join	No direct corresponding operator; however, the $unwindoperator allows for somewhat similar functionality, but with fields embedded within the document.

实例：
[td]

SQL Example	MongoDB Example	Description
SELECT COUNT(*) AS countFROM orders	db.orders.aggregate( [   { $group: { _id: null,               count: { $sum: 1 } } }] )	Count all records fromorders
SELECT SUM(price) AS totalFROM orders	db.orders.aggregate( [   { $group: { _id: null,               total: { $sum: "$price" } } }] )	Sum theprice field from orders，这个非常有用，看官方说明，说_ID是必须，但没想到可以为NULL,
SELECT cust_id,       SUM(price) AStotalFROM ordersGROUP BY cust_id	db.orders.aggregate( [   { $group: { _id: "$cust_id",               total: { $sum: "$price" } } }] )	For each uniquecust_id, sum the pricefield.
SELECT cust_id,       SUM(price) AStotalFROM ordersGROUP BYcust_idORDER BY total	db.orders.aggregate( [   { $group: { _id: "$cust_id",               total: { $sum: "$price" } } },   { $sort: { total: 1 } }] )	For each uniquecust_id, sum the pricefield, results sorted by sum.
SELECT cust_id,       ord_date,      SUM(price) AS totalFROM ordersGROUPBY cust_id, ord_date	db.orders.aggregate( [   { $group: { _id: { cust_id: "$cust_id",                      ord_date: "$ord_date" },               total: { $sum: "$price" } } }] )	For each uniquecust_id,ord_dategrouping, sum the pricefield.
SELECT cust_id, count(*)FROMordersGROUP BY cust_idHAVING count(*)> 1	db.orders.aggregate( [   { $group: { _id: "$cust_id",               count: { $sum: 1 } } },   { $match: { count: { $gt: 1 } } }] )	For cust_idwith multiple records, return thecust_id and the corresponding record count.
SELECT cust_id,       ord_date,      SUM(price) AS totalFROM ordersGROUPBY cust_id, ord_dateHAVING total > 250	db.orders.aggregate( [   { $group: { _id: { cust_id: "$cust_id",                      ord_date: "$ord_date" },               total: { $sum: "$price" } } },   { $match: { total: { $gt: 250 } } }] )	For each uniquecust_id,ord_dategrouping, sum the pricefield and return only where the sum is greater than 250.
SELECT cust_id,       SUM(price) astotalFROM ordersWHERE status ='A'GROUP BY cust_id	db.orders.aggregate( [   { $match: { status: 'A' } },   { $group: { _id: "$cust_id",               total: { $sum: "$price" } } }] )	For each uniquecust_id with status A, sum the pricefield.
SELECT cust_id,       SUM(price) astotalFROM ordersWHERE status ='A'GROUP BY cust_idHAVING total > 250	db.orders.aggregate( [   { $match: { status: 'A' } },   { $group: { _id: "$cust_id",               total: { $sum: "$price" } } },   { $match: { total: { $gt: 250 } } }] )	For each uniquecust_id with status A, sum the pricefield and return only where the sum is greater than 250.
SELECT cust_id,       SUM(li.qty) asqtyFROM orders o,     order_lineitem liWHERE li.order_id = o.idGROUP BYcust_id	db.orders.aggregate( [   { $unwind: "$items" },   { $group: { _id: "$cust_id",               qty: { $sum: "$items.qty" } } }] )	For each uniquecust_id, sum the corresponding line item qtyfields associated with the orders.
SELECT COUNT(*)FROM (SELECT cust_id, ord_date      FROM orders      GROUP BYcust_id, ord_date) as DerivedTable	db.orders.aggregate( [   { $group: { _id: { cust_id: "$cust_id",                      ord_date: "$ord_date" } } },   { $group: { _id: null, count: { $sum: 1 } } }] )