mongodb测试之2门课成绩高于90的学生

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()

 > db.stu.aggregate({$match:{}},         //a=1

{$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}}     //排除你不想要的列,都想要的话就排除个不存在的

)