景区 平台 最新评分

查询数据

db.spot.aggregate([
    {
        $lookup:{
            from:'spot_comment',
            localField:'ota_spot_id',
            foreignField:'ota_spot_id',
            as:'spot_comments'
        }
    },
    {
        $unwind:{
            path:"$spot_comments",
            preserveNullAndEmptyArrays:true
        }
    },
    {
        $group:{
            _id:{ota_spot_id:'$ota_spot_id',spot_name:'$spot_name',ota_id:'$ota_id'},
            c_score:{$first:"$spot_comments.c_score"},
            create_at:{$first:"$spot_comments.create_at"}
        }
    },
    {
        $sort:{create_at:-1}
    },
    {
        $group:{
            _id:{ota_spot_id:'$_id.ota_spot_id',spot_name:'$_id.spot_name'},
            ota_10001_score: {$sum:{$cond : [{$eq : ['$_id.ota_id',10001]},'$c_score',0]}},
            ota_10002_score: {$sum:{$cond : [{$eq : ['$_id.ota_id',10002]},'$c_score',0]}},
            ota_10003_score: {$sum:{$cond : [{$eq : ['$_id.ota_id',10003]},'$c_score',0]}},
            ota_10004_score: {$sum:{$cond : [{$eq : ['$_id.ota_id',10004]},'$c_score',0]}},
            ota_10005_score: {$sum:{$cond : [{$eq : ['$_id.ota_id',10005]},'$c_score',0]}},
        }
    }
])
# $sum 可以换成$first 然后加一个$sort玩玩

返回结果

// 1
{
    "_id": {
        "ota_spot_id": NumberInt("10650528"),
        "spot_name": "侠天下旅游区"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 5
}

// 2
{
    "_id": {
        "ota_spot_id": NumberInt("339"),
        "spot_name": "石燕湖生态旅游公园"
    },
    "ota_10001_score": 5,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 0
}

// 3
{
    "_id": {
        "ota_spot_id": NumberInt("5427075"),
        "spot_name": "石牛寨国家地质公园"
    },
    "ota_10001_score": 5,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 0
}

// 4
{
    "_id": {
        "ota_spot_id": NumberInt("30067"),
        "spot_name": "玻璃桥景区石牛寨"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 5,
    "ota_10005_score": 0
}

// 5
{
    "_id": {
        "ota_spot_id": NumberInt("127339"),
        "spot_name": "玻璃桥景区石牛寨"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 5,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 0
}

// 6
{
    "_id": {
        "ota_spot_id": NumberInt("62931"),
        "spot_name": "石燕湖"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 5,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 0
}

// 7
{
    "_id": {
        "ota_spot_id": NumberInt("103177"),
        "spot_name": "马仁奇峰"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 5
}

// 8
{
    "_id": {
        "ota_spot_id": NumberInt("103113"),
        "spot_name": "玻璃桥景区石牛寨"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 5
}

// 9
{
    "_id": {
        "ota_spot_id": NumberInt("102525"),
        "spot_name": "连云山峡谷漂流"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 5
}

// 10
{
    "_id": {
        "ota_spot_id": NumberInt("100025"),
        "spot_name": "长沙石燕湖"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 5
}

// 11
{
    "_id": {
        "ota_spot_id": NumberInt("162027"),
        "spot_name": "乌金山狂欢谷"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 3
}

// 12
{
    "_id": {
        "ota_spot_id": NumberInt("1515791"),
        "spot_name": "石燕湖"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 4,
    "ota_10005_score": 0
}

// 13
{
    "_id": {
        "ota_spot_id": NumberInt("10829578"),
        "spot_name": "九江东浒寨"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 5
}

// 14
{
    "_id": {
        "ota_spot_id": NumberInt("160416"),
        "spot_name": "九龙江国家森林公园"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 5
}

// 15
{
    "_id": {
        "ota_spot_id": NumberInt("11945662"),
        "spot_name": "湖北阳新天空之城"
    },
    "ota_10001_score": 0,
    "ota_10002_score": 0,
    "ota_10003_score": 0,
    "ota_10004_score": 0,
    "ota_10005_score": 5
}