mongoDB与sql聚合操作对应图
SQL Terms, Functions, and Concepts |
MongoDB Aggregation Operators |
WHERE |
|
GROUP BY |
|
HAVING |
|
SELECT |
|
ORDER BY |
|
LIMIT |
|
SUM() |
|
COUNT() |
|
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 } } }] ) |
最新文章
- 在Excel中制作金字塔条形图
- Create a geoprocessing tool to buffer a layer and retrieve messages____sync
- BZOJ4521: [Cqoi2016]手机号码
- CSS 实现垂直居中的几种方案
- objectARX获取当前图层所有文字样式
- English Learning
- Hubot Slack CoffeeScript
- stardict词典(星际译王)
- 实际用户ID,有效用户ID和设置用户ID
- 浅谈:SAMBA配置设置
- OCA读书笔记(1) - 浏览Oracle数据库架构
- String.Split()功能
- redhat6.4配置yum
- 如何破解加密了的word文档
- CentOS 7 本地安装kubernetes
- docker更改默认仓库地址
- Retrofit+RxJava(2)-基本使用
- 【算法学习】manacher
- Slim安装以及使用【转】
- centos7 修改网卡eth0 关闭ipv6 问题总结
热门文章
- 【状压dp】AC Challenge
- HDU 4489 The King’s Ups and Downs dp
- 3dContactPointAnnotationTool开发日志(二七)
- 2nd 阅读构建之法有感
- CentOS系统iptables防火墙的启动、停止以及开启关闭端口的操作
- php中扩展pecl与pear
- Command Network OpenJ_Bailian - 3436(最小有向生成树模板题)
- Visual Format Language(VFL)视图约束
- mysql权限管理,用户管理
- NOIP2015Day1T3斗地主(DFS)