MONGODB 与sql聚合操作对应图
来源:互联网 发布:麦克德莫特 数据 编辑:程序博客网 时间:2024/05/22 16:54
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 } } }] )
- MONGODB 与sql聚合操作对应图
- mongodb与sql聚合操作对应图
- mongodb与sql聚合操作对应图
- MONGODB 与sql聚合操作对应图
- mongodb与sql聚合对应图 M
- Mongodb 3.2 SQL对应聚合 官方
- MongoDB文档翻译-CRUD操作-SQL与MongoDB对应表
- MongoDB 与 SQL 的语法对应关系
- mongodb与SQL对应关系表
- MongoDB中的聚合操作
- mongodb聚合函数操作
- mongodb aggregate 聚合操作
- mongodb 索引、聚合操作
- MongoDB 聚合操作
- MongoDB的聚合操作
- MongoDB聚合操作Aggregation
- MongoDB 聚合操作
- mongodb代码整理五:sql聚合函数与mongodb聚合函数对比
- 安装pip-9.0.1-py2.py3-none-any.whl
- JavaScript面向对象程序设计——属性
- Linux 习题2
- 前端面试-JavaScript篇
- 小蜜蜂单片机串口发送字符串
- MONGODB 与sql聚合操作对应图
- 搜索菜谱二级列表
- 简单神经网络实现 02
- socket之重叠io
- 从数据库中查询,两个表中某个字段相等时
- chrome 改变网页主题 设置黑色主题网页
- NDK开发--CMake篇
- 企业用机械设备行业ERP有哪些作用?
- 判断一个数是2的整数次幂