mongoDB的数据去重distinct（十三）

要实现mysql中的sql语句，具体如下：

select max(_id),dept from test1 group by dept;

1.在mongodb中写入数据

db.test1.insert({"dept": "A", "item": { "sku": "111", "color": "red" }, "sizes": [ "S", "M" ] })
db.test1.insert({"dept": "A", "item": { "sku": "111", "color": "blue" }, "sizes": [ "M", "L" ] })
db.test1.insert({"dept": "B", "item": { "sku": "222", "color": "blue" }, "sizes": "S" })

2.mongodb中有distinct，但是只能实现如下效果

db.inventory.distinct("dept") 

去重后统计数量：

db.inventory.distinct("dept").length

3.此时只能利用aggregate来完成

db.test1.aggregate(
{
     $group:{_id:"$dept",minvalue:{$min:"$_id"}}
}
)

如果要实现

select max(_id),dept from test1 where xx=? group by dept;

可以增加 $match:{xx:'chenzong'} 到aggregate中去

4.如果需要实现以下sql

select * from test1 where _id in (
select max(_id) from test1group by dept
); 

此时可以通过以下2步来完成：

(1).创建分组去重后的视图View_test1

db.createView("View_test1","test1",[{$group:{_id:"$dept",minvalue:{$min:"$_id"}}}])

(2).利用mongodb的$lookup来完成  

db.View_test1.aggregate([{
   $lookup:{
from:"test1",
   localField:"minvalue",
   foreignField:"_id",
as:"aa_docs"
}
}])

5.如果需要实现以下sql（去重后统计数量）

  select count(1)
from (select fromUser, count(1)
from ldmError
where hosCode = '00002'
group by fromUser);

对应的mongodb语句为：

db.ldmError.group({
           key:{fromUser:1},
           cond:{hosCode:"00002"},
           reduce:function(curr,result){
           result.total++;
},
           initial:{total:0}
}).length