[原]mongodb测试之2门课成绩高于90的学生
book_mmicky发表于 2014-05-13 15:21:42
1：启动mongodb后，插入测试数据
> use school
> db.stu.drop()
> db.stu.insert({"stu_id":"001","name":"mjh","chinese":95,"english":85,"math":93,"sport":98,"chemical":88})
> db.stu.insert({"stu_id":"002","name":"wyy","chinese":99,"pysical":98,"sport":86,"drawing":85})
> db.stu.insert({"stu_id":"003","name":"myf","chinese":97,"pysical":89,"drawing":84})
> db.stu.find()
方法一：内外循环，找出有不相同的课程有大于等于90的文档，缺点找3门或4门90以上的可能就悲剧了。
db.stu.find({"$where":function(){
for (var current in this){
for (var other in this){
if (current != other && this[current]>=90 && this[other]>=90){
return true;
}
}
}
return false;
}},{name:1,_id:0});
方法二：计数器查找大于等于90的文档，可以方便地找出大于等于N门90以上的课程，缺点是文档中不能有其他的数值元素。
db.stu.find({"$where":function(){
i=0;
for (var current in this){
if (this[current]>=90) {
i=i+1;
}
}
if (i>=2)
return true;
else
return false;
}},{name:1,_id:0});
成绩3门90以上
db.stu.find({"$where":function(){
i=0;
for (var current in this){
if (this[current]>=90) {
i=i+1;
}
}
if (i>=3)
return true;
else
return false;
}},{name:1,_id:0});
方法三：将成绩放在成绩单内嵌文档里，然后对内嵌文档查询。
> db.stu.drop()
> db.stu.insert({"stu_id":"001","name":"mjh","score_sheet":[{"course":"chinese","score":95},
{"course":"english","score":85},{"course":"math","score":93},{"course":"sport","score":98},
{"course":"chemical","score":88}]})
> db.stu.insert({"stu_id":"002","name":"wyy","score_sheet":[{"course":"chinese","score":99},
{"course":"pysical","score":98},{"course":"sport","score":86},{"course":"drawing","score":85}]})
> db.stu.insert({"stu_id":"003","name":"myf","score_sheet":[{"course":"chinese","score":97},
{"course":"pysical","score":89},{"course":"drawing","score":84}]})
> db.stu.find()
{$unwind:"$score_sheet"}, //展开task数组
{$match:{"score_sheet.score":{"$gte":90}}},
{$group:{_id:"$_id",name:{"$first":"$name"},count:{$sum:1}}},
{$project:{_id:0,name:1,count:1}} //排除你不想要的列,都想要的话就排除个不存在的
)
