MongoDB 查询简介

小咪咪 2022-12-22 12:29 250阅读 0赞

备注:  
MongoDB 4.2 版本

### 文章目录 ###

*  一.MongoDB查询介绍
 *   *  2.1 所有记录
     *  2.2 过滤记录
     *  2.3 字段筛选
     *  2.4 排序
     *  2.5 比大小
     *  2.6 正则(模糊匹配)
     *  2.7 or操作
     *  2.8 仅返回1条
     *  2.9 count聚合
     *  2.10 查询计划
     *  2.11 sum
     *   *  2.11.1 分组汇总
         *  2.11.2 avg
         *  2.11.3 min
         *  2.11.4 max
         *  2.11.5 push
         *  2.11.6 addToSet
         *  2.11.7 first
         *  2.11.8 last
         *  2.11.9 聚合汇总用例

测试数据:

-- 类似Oracle里scott下的emp表
    db.emp.insert([
    {EMPNO:7369,ENAME:'SMITH',JOB:'CLERK',MGR:7902,HIREDATE:new Date(1980,12,17),SAL:800,DEPTNO:20,dname:'RESEARCH',loc:'DALLAS'},
    {EMPNO:7499,ENAME:'ALLEN',JOB:'SALESMAN',MGR:7698,HIREDATE:new Date(1981,2,20),SAL:1600,COMM:300,DEPTNO:30,dname:'SALES',loc:'CHICAGO'},
    {EMPNO:7521,ENAME:'WARD',JOB:'SALESMAN',MGR:7698,HIREDATE:new Date(1981,2,22),SAL:1250,COMM:500,DEPTNO:30,dname:'SALES',loc:'CHICAGO'},
    {EMPNO:7566,ENAME:'JONES',JOB:'MANAGER',MGR:7839,HIREDATE:new Date(1981,4,2),SAL:2975,DEPTNO:20,dname:'RESEARCH',loc:'DALLAS'},
    {EMPNO:7654,ENAME:'MARTIN',JOB:'SALESMAN',MGR:7698,HIREDATE:new Date(1981,9,28),SAL:1250,COMM:1400,DEPTNO:30,dname:'SALES',loc:'CHICAGO'},
    {EMPNO:7698,ENAME:'BLAKE',JOB:'MANAGER',MGR:7839,HIREDATE:new Date(1981,5,1),SAL:2850,DEPTNO:30,dname:'SALES',loc:'CHICAGO'},
    {EMPNO:7782,ENAME:'CLARK',JOB:'MANAGER',MGR:7839,HIREDATE:new Date(1981,6,9),SAL:2450,DEPTNO:10,dname:'ACCOUNTING',loc:'NEW YORK'},
    {EMPNO:7839,ENAME:'KING',JOB:'PRESIDENT',HIREDATE:new Date(1981,11,17),SAL:5000,DEPTNO:10,dname:'ACCOUNTING',loc:'NEW YORK'},
    {EMPNO:7844,ENAME:'TURNER',JOB:'SALESMAN',MGR:7698,HIREDATE:new Date(1981,2,20),SAL:1500,COMM:0,DEPTNO:30,dname:'SALES',loc:'CHICAGO'},
    {EMPNO:7900,ENAME:'JAMES',JOB:'CLERK',MGR:7698,HIREDATE:new Date(1981,8,9),SAL:950,DEPTNO:30,dname:'SALES',loc:'CHICAGO'},
    {EMPNO:7902,ENAME:'FORD',JOB:'ANALYST',MGR:7566,HIREDATE:new Date(1981,12,3),SAL:3000,DEPTNO:20,dname:'RESEARCH',loc:'DALLAS'},
    {EMPNO:7934,ENAME:'MILLER',JOB:'CLERK',MGR:7782,HIREDATE:new Date(1982,1,23),SAL:1300,DEPTNO:10,dname:'ACCOUNTING',loc:'NEW YORK'},
    {DEPTNO:40,dname:'OPERATIONS',loc:'BOSTON'}
    ])

# 一.MongoDB查询介绍 #

<table> 
 <thead> 
  <tr> 
   <th>作用</th> 
   <th>SQL</th> 
   <th>MongoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>所有记录</td> 
   <td>select * from emp</td> 
   <td>db.emp.find()</td> 
  </tr> 
  <tr> 
   <td>empno=6379</td> 
   <td>select * from emp where empno=7369</td> 
   <td>db.emp.find({EMPNO:7369})</td> 
  </tr> 
  <tr> 
   <td>字段筛选</td> 
   <td>select empno,ename from emp where empno=7369</td> 
   <td>db.emp.find({EMPNO:7369},{EMPNO:1,ENAME:1})</td> 
  </tr> 
  <tr> 
   <td>排序</td> 
   <td>select * from emp where deptno = 10 order by ename</td> 
   <td>db.emp.find({DEPTNO:10}).sort({ENAME:1})</td> 
  </tr> 
  <tr> 
   <td>比大小</td> 
   <td>select * from emp where deptno &gt; 20</td> 
   <td>db.emp.find({‘DEPTNO’:{$gt:20}})</td> 
  </tr> 
  <tr> 
   <td>正则(模糊匹配)</td> 
   <td>select * from emp where ename like ‘S%’</td> 
   <td>db.emp.find({ENAME:/^S/})</td> 
  </tr> 
  <tr> 
   <td>or操作</td> 
   <td>select * from emp where deptno = 10 or ename = ‘SMITH’</td> 
   <td>db.emp.find({$or :[{DEPTNO:10},{ENAME:‘SMITH’}]})</td> 
  </tr> 
  <tr> 
   <td>仅返回1条</td> 
   <td>select * from emp limit 1</td> 
   <td>db.user.findOne()</td> 
  </tr> 
  <tr> 
   <td>count聚合</td> 
   <td>select count(ename) from emp</td> 
   <td>db.emp.find({ENAME:{’$exists’: true}}).count()</td> 
  </tr> 
  <tr> 
   <td>查询计划</td> 
   <td>explain select * from emp where empno = 7369</td> 
   <td>db.user.find({EMPNO:7369}).explain()</td> 
  </tr> 
 </tbody> 
</table>

## 2.1 所有记录 ##

测试记录:

> db.emp.find()
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b3"), "EMPNO" : 7369, "ENAME" : "SMITH", "JOB" : "CLERK", "MGR" : 7902, "HIREDATE" : ISODate("1981-01-16T16:00:00Z"), "SAL" : 800, "DEPTNO" : 20, "dname" : "RESEARCH", "loc" : "DALLAS" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b4"), "EMPNO" : 7499, "ENAME" : "ALLEN", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : ISODate("1981-03-19T16:00:00Z"), "SAL" : 1600, "COMM" : 300, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b5"), "EMPNO" : 7521, "ENAME" : "WARD", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : ISODate("1981-03-21T16:00:00Z"), "SAL" : 1250, "COMM" : 500, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b6"), "EMPNO" : 7566, "ENAME" : "JONES", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : ISODate("1981-05-01T16:00:00Z"), "SAL" : 2975, "DEPTNO" : 20, "dname" : "RESEARCH", "loc" : "DALLAS" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b7"), "EMPNO" : 7654, "ENAME" : "MARTIN", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : ISODate("1981-10-27T16:00:00Z"), "SAL" : 1250, "COMM" : 1400, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b8"), "EMPNO" : 7698, "ENAME" : "BLAKE", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : ISODate("1981-05-31T16:00:00Z"), "SAL" : 2850, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b9"), "EMPNO" : 7782, "ENAME" : "CLARK", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : ISODate("1981-07-08T16:00:00Z"), "SAL" : 2450, "DEPTNO" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995ba"), "EMPNO" : 7839, "ENAME" : "KING", "JOB" : "PRESIDENT", "HIREDATE" : ISODate("1981-12-16T16:00:00Z"), "SAL" : 5000, "DEPTNO" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995bb"), "EMPNO" : 7844, "ENAME" : "TURNER", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : ISODate("1981-03-19T16:00:00Z"), "SAL" : 1500, "COMM" : 0, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995bc"), "EMPNO" : 7900, "ENAME" : "JAMES", "JOB" : "CLERK", "MGR" : 7698, "HIREDATE" : ISODate("1981-09-08T16:00:00Z"), "SAL" : 950, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995bd"), "EMPNO" : 7902, "ENAME" : "FORD", "JOB" : "ANALYST", "MGR" : 7566, "HIREDATE" : ISODate("1982-01-02T16:00:00Z"), "SAL" : 3000, "DEPTNO" : 20, "dname" : "RESEARCH", "loc" : "DALLAS" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995be"), "EMPNO" : 7934, "ENAME" : "MILLER", "JOB" : "CLERK", "MGR" : 7782, "HIREDATE" : ISODate("1982-02-22T16:00:00Z"), "SAL" : 1300, "DEPTNO" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995bf"), "DEPTNO" : 40, "dname" : "OPERATIONS", "loc" : "BOSTON" }
    > 
    >

## 2.2 过滤记录 ##

测试记录:

> db.emp.find({empno:7369})
    > 
    > db.emp.find({EMPNO:7369})
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b3"), "EMPNO" : 7369, "ENAME" : "SMITH", "JOB" : "CLERK", "MGR" : 7902, "HIREDATE" : ISODate("1981-01-16T16:00:00Z"), "SAL" : 800, "DEPTNO" : 20, "dname" : "RESEARCH", "loc" : "DALLAS" }
    >

## 2.3 字段筛选 ##

测试记录:

> db.emp.find({EMPNO:7369},{EMPNO:1,ENAME:1})
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b3"), "EMPNO" : 7369, "ENAME" : "SMITH" }
    >

## 2.4 排序 ##

测试记录:

> db.emp.find({DEPTNO:10}).sort({ENAME:1})
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b9"), "EMPNO" : 7782, "ENAME" : "CLARK", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : ISODate("1981-07-08T16:00:00Z"), "SAL" : 2450, "DEPTNO" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995ba"), "EMPNO" : 7839, "ENAME" : "KING", "JOB" : "PRESIDENT", "HIREDATE" : ISODate("1981-12-16T16:00:00Z"), "SAL" : 5000, "DEPTNO" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995be"), "EMPNO" : 7934, "ENAME" : "MILLER", "JOB" : "CLERK", "MGR" : 7782, "HIREDATE" : ISODate("1982-02-22T16:00:00Z"), "SAL" : 1300, "DEPTNO" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
    >

## 2.5 比大小 ##

测试记录:

> db.emp.find({'DEPTNO':{$gt:20}})
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b4"), "EMPNO" : 7499, "ENAME" : "ALLEN", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : ISODate("1981-03-19T16:00:00Z"), "SAL" : 1600, "COMM" : 300, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b5"), "EMPNO" : 7521, "ENAME" : "WARD", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : ISODate("1981-03-21T16:00:00Z"), "SAL" : 1250, "COMM" : 500, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b7"), "EMPNO" : 7654, "ENAME" : "MARTIN", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : ISODate("1981-10-27T16:00:00Z"), "SAL" : 1250, "COMM" : 1400, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b8"), "EMPNO" : 7698, "ENAME" : "BLAKE", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : ISODate("1981-05-31T16:00:00Z"), "SAL" : 2850, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995bb"), "EMPNO" : 7844, "ENAME" : "TURNER", "JOB" : "SALESMAN", "MGR" : 7698, "HIREDATE" : ISODate("1981-03-19T16:00:00Z"), "SAL" : 1500, "COMM" : 0, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995bc"), "EMPNO" : 7900, "ENAME" : "JAMES", "JOB" : "CLERK", "MGR" : 7698, "HIREDATE" : ISODate("1981-09-08T16:00:00Z"), "SAL" : 950, "DEPTNO" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995bf"), "DEPTNO" : 40, "dname" : "OPERATIONS", "loc" : "BOSTON" }
    >

## 2.6 正则(模糊匹配) ##

测试记录:

> db.emp.find({ENAME:/^S/}) 
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b3"), "EMPNO" : 7369, "ENAME" : "SMITH", "JOB" : "CLERK", "MGR" : 7902, "HIREDATE" : ISODate("1981-01-16T16:00:00Z"), "SAL" : 800, "DEPTNO" : 20, "dname" : "RESEARCH", "loc" : "DALLAS" }
    >

## 2.7 or操作 ##

测试记录:

> db.emp.find({$or :[{DEPTNO:10},{ENAME:'SMITH'}]})
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b3"), "EMPNO" : 7369, "ENAME" : "SMITH", "JOB" : "CLERK", "MGR" : 7902, "HIREDATE" : ISODate("1981-01-16T16:00:00Z"), "SAL" : 800, "DEPTNO" : 20, "dname" : "RESEARCH", "loc" : "DALLAS" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995b9"), "EMPNO" : 7782, "ENAME" : "CLARK", "JOB" : "MANAGER", "MGR" : 7839, "HIREDATE" : ISODate("1981-07-08T16:00:00Z"), "SAL" : 2450, "DEPTNO" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995ba"), "EMPNO" : 7839, "ENAME" : "KING", "JOB" : "PRESIDENT", "HIREDATE" : ISODate("1981-12-16T16:00:00Z"), "SAL" : 5000, "DEPTNO" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
    { "_id" : ObjectId("5fa4b2eaeae97236d30995be"), "EMPNO" : 7934, "ENAME" : "MILLER", "JOB" : "CLERK", "MGR" : 7782, "HIREDATE" : ISODate("1982-02-22T16:00:00Z"), "SAL" : 1300, "DEPTNO" : 10, "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
    > 
    >

## 2.8 仅返回1条 ##

测试记录:

> db.emp.findOne()
    {
            "_id" : ObjectId("5fa4b2eaeae97236d30995b3"),
            "EMPNO" : 7369,
            "ENAME" : "SMITH",
            "JOB" : "CLERK",
            "MGR" : 7902,
            "HIREDATE" : ISODate("1981-01-16T16:00:00Z"),
            "SAL" : 800,
            "DEPTNO" : 20,
            "dname" : "RESEARCH",
            "loc" : "DALLAS"
    }
    >

## 2.9 count聚合 ##

测试记录:

> db.emp.find({ENAME:{'$exists': true}}).count()
    12
    >

## 2.10 查询计划 ##

测试记录:

> db.user.find({EMPNO:7369}).explain()
    {
            "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "zqs.user",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                            "EMPNO" : {
                                    "$eq" : 7369
                            }
                    },
                    "winningPlan" : {
                            "stage" : "EOF"
                    },
                    "rejectedPlans" : [ ]
            },
            "serverInfo" : {
                    "host" : "10-31-1-124",
                    "port" : 27017,
                    "version" : "4.2.10",
                    "gitVersion" : "88276238fa97b47c0ef14362b343c5317ecbd739"
            },
            "ok" : 1
    }
    >

## 2.11 sum ##

MongoDB 中聚合(aggregate)主要用于处理数据(诸如统计平均值，求和等)，并返回计算后的数据结果

语法:

db.collection.aggregate( [ <stage1>, <stage2>, ... ] )

<table> 
 <thead> 
  <tr> 
   <th>表达式</th> 
   <th>描述</th> 
   <th>实例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>$sum</td> 
   <td>计算总和</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>$avg</td> 
   <td>计算平均值</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>$min</td> 
   <td>获取集合中所有文档对应值得最小值</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>$max</td> 
   <td>获取集合中所有文档对应值得最大值</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>$push</td> 
   <td>在结果文档中插入值到一个数组中</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>$addToSet</td> 
   <td>在结果文档中插入值到一个数组中，但不创建副本</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>$first</td> 
   <td>根据资源文档的排序获取第一个文档数据</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>$last</td> 
   <td>根据资源文档的排序获取最后一个文档数据</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

### 2.11.1 分组汇总 ###

代码:

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",emp_count : {$sum: 1}}}
    ])
    -- 等价于下面的sql
    select deptno,count(*) from emp group by deptno
    
    db.emp.aggregate([
    {$group : {_id : "$DEPTNO",sum_sal : {$sum: '$SAL'}}}
    ])
    -- 等价于下面的sql
    select deptno,sum(sal) from emp group by deptno

测试记录:

> db.emp.aggregate([
    ... {$group : {_id : "$DEPTNO",emp_count : {$sum: 1}}}
    ... ])
    { "_id" : 30, "emp_count" : 6 }
    { "_id" : 20, "emp_count" : 3 }
    { "_id" : 40, "emp_count" : 1 }
    { "_id" : 10, "emp_count" : 3 }
    > 
    
    > db.emp.aggregate([
    ... {$group : {_id : "$DEPTNO",sum_sal : {$sum: '$SAL'}}}
    ... ])
    { "_id" : 20, "sum_sal" : 6775 }
    { "_id" : 40, "sum_sal" : 0 }
    { "_id" : 30, "sum_sal" : 9400 }
    { "_id" : 10, "sum_sal" : 8750 }
    >

### 2.11.2 avg ###

代码:

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",avg_sal : {$avg: '$SAL'}}}
    ])
    -- 等价于下面这个sql
    select deptno,avg(sal) avg_sal from emp

测试记录:

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",avg_sal : {$avg: '$SAL'}}}
    ])

### 2.11.3 min ###

代码：

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",min_sal : {$min: '$SAL'}}}
    ])
    -- 等价于下面的sql
    select deptno,min(sal) as min_sal from emp group by deptno

测试记录:

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",min_sal : {$min: '$SAL'}}}
    ])

### 2.11.4 max ###

代码：

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",max_sal : {$max: '$SAL'}}}
    ])
    -- 等价于下面的sql
    select deptno,max(sal) as max_sal from emp group by deptno

测试记录:

> db.emp.aggregate([
    ... {$group : {_id : "$DEPTNO",max_sal : {$max: '$SAL'}}}
    ... ])
    { "_id" : 20, "max_sal" : 3000 }
    { "_id" : 40, "max_sal" : null }
    { "_id" : 30, "max_sal" : 2850 }
    { "_id" : 10, "max_sal" : 5000 }
    >

### 2.11.5 push ###

代码：

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",emps : {$push: '$ENAME'}}}
    ])
    -- 等价于mysql里如下sql
    select deptno,group_concat(ename) from emp group by deptno;

测试记录:

> db.emp.aggregate([
    ... {$group : {_id : "$DEPTNO",emps : {$push: '$ENAME'}}}
    ... ])
    { "_id" : 20, "emps" : [ "SMITH", "JONES", "FORD" ] }
    { "_id" : 40, "emps" : [ ] }
    { "_id" : 30, "emps" : [ "ALLEN", "WARD", "MARTIN", "BLAKE", "TURNER", "JAMES" ] }
    { "_id" : 10, "emps" : [ "CLARK", "KING", "MILLER" ] }
    >

### 2.11.6 addToSet ###

代码：

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",emps : {$addToSet: '$ENAME'}}}
    ])
    -- 等价于下面的sql
    select  deptno,group_concat(ename)
    from (select distinct deptno,ename from emp) e
    group by deptno

测试记录:

> db.emp.aggregate([
    ... {$group : {_id : "$DEPTNO",emps : {$addToSet: '$ENAME'}}}
    ... ])
    { "_id" : 20, "emps" : [ "SMITH", "JONES", "FORD" ] }
    { "_id" : 40, "emps" : [ ] }
    { "_id" : 30, "emps" : [ "BLAKE", "TURNER", "JAMES", "WARD", "ALLEN", "MARTIN" ] }
    { "_id" : 10, "emps" : [ "KING", "CLARK", "MILLER" ] }
    >

### 2.11.7 first ###

代码：

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",first_emp : {$first: '$ENAME'}}}
    ])
    -- 这个用sql实现略微复杂

测试记录:

> db.emp.aggregate([
    ... {$group : {_id : "$DEPTNO",first_emp : {$first: '$ENAME'}}}
    ... ])
    { "_id" : 20, "first_emp" : "SMITH" }
    { "_id" : 40, "first_emp" : null }
    { "_id" : 30, "first_emp" : "ALLEN" }
    { "_id" : 10, "first_emp" : "CLARK" }

### 2.11.8 last ###

代码：

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",last_emp : {$last: '$ENAME'}}}
    ])
    -- 这个用sql实现略微复杂

测试记录:

> db.emp.aggregate([
    ... {$group : {_id : "$DEPTNO",last_emp : {$last: '$ENAME'}}}
    ... ])
    { "_id" : 30, "last_emp" : "JAMES" }
    { "_id" : 20, "last_emp" : "FORD" }
    { "_id" : 40, "last_emp" : null }
    { "_id" : 10, "last_emp" : "MILLER" }

### 2.11.9 聚合汇总用例 ###

代码:

db.emp.aggregate([
    {$group : {_id : "$DEPTNO",
                emp_count : {$sum: 1},
                avg_sal : {$avg: '$SAL'},
                min_sal : {$min: '$SAL'},
                max_sal : {$max: '$SAL'},
                emps1 : {$push: '$ENAME'},
                emps2 : {$addToSet: '$ENAME'},
                first_emp : {$first: '$ENAME'},
                last_emp : {$last: '$ENAME'}         
     }}
    ])

测试记录；

> db.emp.aggregate([
    ... {$group : {_id : "$DEPTNO",
    ...             emp_count : {$sum: 1},
    ...             avg_sal : {$avg: '$SAL'},
    ...             min_sal : {$min: '$SAL'},
    ...             max_sal : {$max: '$SAL'},
    ...             emps1 : {$push: '$ENAME'},
    ...             emps2 : {$addToSet: '$ENAME'},
    ...             first_emp : {$first: '$ENAME'},
    ...             last_emp : {$last: '$ENAME'}         
    ...  }}
    ... ])
    { "_id" : 30, "emp_count" : 6, "avg_sal" : 1566.6666666666667, "min_sal" : 950, "max_sal" : 2850, "emps1" : [ "ALLEN", "WARD", "MARTIN", "BLAKE", "TURNER", "JAMES" ], "emps2" : [ "BLAKE", "TURNER", "JAMES", "WARD", "ALLEN", "MARTIN" ], "first_emp" : "ALLEN", "last_emp" : "JAMES" }
    { "_id" : 20, "emp_count" : 3, "avg_sal" : 2258.3333333333335, "min_sal" : 800, "max_sal" : 3000, "emps1" : [ "SMITH", "JONES", "FORD" ], "emps2" : [ "SMITH", "FORD", "JONES" ], "first_emp" : "SMITH", "last_emp" : "FORD" }
    { "_id" : 40, "emp_count" : 1, "avg_sal" : null, "min_sal" : null, "max_sal" : null, "emps1" : [ ], "emps2" : [ ], "first_emp" : null, "last_emp" : null }
    { "_id" : 10, "emp_count" : 3, "avg_sal" : 2916.6666666666665, "min_sal" : 1300, "max_sal" : 5000, "emps1" : [ "CLARK", "KING", "MILLER" ], "emps2" : [ "KING", "CLARK", "MILLER" ], "first_emp" : "CLARK", "last_emp" : "MILLER" }
    >