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) AS totalFROM 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) AS totalFROM ordersGROUP BY cust_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 ordersGROUP BY 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()FROM ordersGROUP 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 ordersGROUP BY 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) as totalFROM 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) as totalFROM 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) as qtyFROM orders o, order_lineitem liWHERE li.order_id = o.idGROUP BY cust_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 BY cust_id, ord_date) as DerivedTable
db.orders.aggregate( [ { $group: { _id: { cust_id: "$cust_id", ord_date: "$ord_date" } } }, { $group: { _id: null, count: { $sum: 1 } } }] )