数据准备
db.emps.insertMany([
{name:'王铁锤',job:'职员',salary:3000,deptno:1001},
{name:'王磊',job:'职员',salary:3000,deptno:1001},
{name:'李丹',job:'经理',salary:9000,deptno:1001},
{name:'赵晓梅',job:'经理',salary:9560,deptno:1001},
{name:'钱忠国',job:'职员',salary:4000,deptno:1001},
{name:'孙悟空',job:'职员',salary:6000,deptno:1001},
{name:'张翠花',job:'职员',salary:3000,deptno:1002},
{name:'岳绮',job:'职员',salary:5000,deptno:1002},
{name:'小张',job:'经理',salary:7000,deptno:1002},
{name:'王明',job:'经理',salary:7560,deptno:1002},
{name:'张伟',job:'职员',salary:6000,deptno:1002},
{name:'冯小帅',job:'职员',salary:5000,deptno:1002},
{name:'周世博',job:'职员',salary:5000,deptno:1002},
{name:'王强',job:'老板',salary:14000,deptno:0},
]);
$group
- 查询每个职位的总工资：
db.emps.aggregate([{"$group":{_id:'$job',total:{$sum:'$salary'}}}]);
显示结果：
{ "_id" : "老板", "total" : 14000 }
{ "_id" : "经理", "total" : 33120 }
{ "_id" : "职员", "total" : 40000 }
- 每个职位的平均工资：
db.emps.aggregate([{"$group":{_id:'$job',avg:{$avg:'$salary'}}}]);
显示结果：
{ "_id" : "老板", "avg" : 14000 }
{ "_id" : "经理", "avg" : 8280 }
{ "_id" : "职员", "avg" : 4444.444444444444 }
- 求出每个职位的最高与最低工资：
db.emps.aggregate([{"$group":{_id:"$job",max:{"$max":'$salary'},min:{"$min":'$salary'}}}]);
显示结果：
{ "_id" : "老板", "max" : 14000, "min" : 14000 }
{ "_id" : "经理", "max" : 9560, "min" : 7000 }
{ "_id" : "职员", "max" : 6000, "min" : 3000 }
- 计算出每个职位的工资数据(数组形式显示)
db.emps.aggregate([{"$group":{_id:"$job",salary:{'$push':'$salary'}}}]);
显示结果：
{ "_id" : "老板", "salary" : [ 14000 ] }
{ "_id" : "经理", "salary" : [ 9000, 9560, 7000, 7560 ] }
{ "_id" : "职员", "salary" : [ 3000, 3000, 4000, 6000, 3000, 5000,
6000, 5000, 5000 ] }
- 计算每个职位的人员
db.emps.aggregate([{"$group":{_id:"$job",persons:{"$push":"$name"}}}]);
显示结果：
{ "_id" : "老板", "persons" : [ "王强" ] }
{ "_id" : "经理", "persons" : [ "李丹", "赵晓梅", "小张", "王明" ] }
{ "_id" : "职员", "persons" : [ "王铁锤", "王磊", "钱忠国", "孙悟空",
"张翠花", "岳绮", "张伟", "冯小帅", "周世博" ] }
$project
- 显示工资大于4000小于9000的员工，显示name,salary,job字段
db.emps.aggregate([
{$match:{"salary":{"$gt":4000,"$lt":9000}}},
{"$project":{_id:0,name:1,salary:1,job:1}}
]);
