mongodb常用语法
// Employee表 { "_id" : "9e794fb9-12dc-457c-8c5a-69fe45c57685", "No" : 2018090821, "Name" : "Kelly", "Age" : 18, "Addresses":[ {"Type" : "Home", "Location" : "Beijing"}, {"Type" : "Office", "Location" : "Beijing"} ] } // 分页过滤查找 db.Employee.aggregate([ { $match:{"Name":"Kelly"}}, { $project:{"Name":1, "Age":1}}, { $sort:{"Age":1}}, { $skip:1000}, { $limit:30} ],{allowDiskUse:true}) // 查找不在北京工作的员工(数组操作) db.Employee.aggregate([ {"$match": {"Type" : "Office", "Addresses": {$not:{$elemMatch:{"Location": "Beijing"}}}}} ],{allowDiskUse:true}) var result = db.Employee.distinct("No", {"Type" : "Office", "Location": "Beijing"}) db.Employee.aggregate([ {"$match": {"No": {$nin: result}}} ],{allowDiskUse:true}) // 查找在北京工作,且有2个以上住宅的员工 db.Employee.aggregate([ {"$match": {"Type" : "Office", "Location": "Beijing"}}, {"$unwind": "$Addresses"}, {"$match": {"Addresses.Type": "House"}}, {"$group": {_id: "$No", "count": {"$sum": 1}}}, {"$match": {"count": {"$gt": 2}}}, {"sort": {"count":1}} ],{allowDiskUse:true}) // 查找年龄为23-26,名字为Lily或Sam的员工 db.Employee.find({$or:[{"name":"Lily"},{"name":"Sam"}], "Age":{$gte: 23, $lte: 26}}, {"No":1, "Name":1, "_id":0})
select country, province from dbo.regions group by country, province having count(distinct city)>1 order by country, province
db.getCollection('dbo.regions').aggregate([
{$group:{_id:{"country" :"$country" ,"province":"$province", "city":"$city"}}}
,{$group:{_id:{"country" :"$_id.country" ,"province":"$_id.province"}, count: {$sum:1}}}
,{$match:{count:{$gt:1}}}
// ,{$project: {"country" :"$_id.country" ,"province":"$_id.province", "_id":0}}
// ,{$sort: {"country" : 1, "province":1}}
// ,{$group:{_id:{"country" :"$country" }, count: {$sum:1}}},
])