关系型数据库表（集合）与表（集合）之间的关系聚合查询

关系型数据库表与表的几种关系

1、一对一的关系

例如：一个人对应一个身份证号，即为一对一的关系

2、一对多的关系

例如：1个班级对应多名学生，即为一对多的关系

3、多对多的关系

例如：一个学生可以选择多门课程、一门课程可以被多个学生选修，彼此的对应关系，即是多对多的关系。

多对多的通常会建立一个中间表来存储多对多的关系

聚合管道：

aggregation

db.order.aggregate([
    {$match:{state:"A"}},//匹配order表中state为A的数据
    {$group:{_id:"$cus_id",total:{$sum:"$amount"}}},//根据cus_id来分组，用每一组的amount来求和
])

表的聚合管道主要用来做表的关联查询和数据统计

聚合管道的常见操作符：

$project : 增加、删除、重命名字段

$match : 条件匹配，只满足条件的文档才能进入下一阶段

$limit : 限制结果的数量

$skip : 跳过文档的数量

$sort : 条件排序

$group : 条件组合结果统计

$lookup : $lookup可以引入其他集合的数据（表关联查询）

order表中有这些数据

order_item表中有这些数据

一个订单对应多个商品

$project 筛选列控制显示列中的那些字段

db.order.aggregate([
    {
        $project:{order_id:1,trade_no:1,all_price:1} //筛选出order_id、trade_no、all_price 这3个属性 1|true：显示  0|false:不显示
    }
])

$match 用于过滤文档，用法类似于find方法的参数

db.order.aggregate([
    {
        $project:{order_id:1,trade_no:1,all_price:1}
    },
    {
        $match:{"all_price":{$gte:90}} //筛选出all_price >= 90 的数据
    }
])

$group 将集合中的文档进行分组，可用于统计结果

　　　　统计每个订单的订单数量，按照订单分组

db.order_item.aggregate([
    {
        $group:{_id:"$order_id",total:{$sum:"$num"}}  //以order_id进行分组 求出每组num属性的和
    }
])

$sort

对集合中的文档进行排序

db.order.aggregate([
    {
        $project:{order_id:1,trade_no:1,all_price:1}
    },
    {
        $match:{"all_price":{$gte:90}} 
    },
    {
        $sort:{"all_price":-1} //以all_price进行倒叙排序 1：正序 2：倒叙
    }
])

$limit

限制文档条数

db.order.aggregate([
    {
        $project:{order_id:1,trade_no:1,all_price:1}
    },
    {
        $match:{"all_price":{$gte:90}} 
    },
    {
        $sort:{"all_price":-1} 
    },
    {
        $limit:1 //限制1条
    }
])

$skip

跳过几条

db.order.aggregate([
    {
        $project:{order_id:1,trade_no:1,all_price:1}
    },
    {
        $match:{"all_price":{$gte:90}} 
    },
    {
        $sort:{"all_price":-1} 
    },
    {
        $limit:2 
    },
    {
        $skip:1 //跳过1条
    }
])

$lookup

表关联

实现下面结果

[
    {
        order_id:'',
        trade_no:'',
        items:[
            {title:'鼠标',price:20},
            {title:'键盘',price:20}
        ]
    }
]

分析trade_no在order表内剩余属性在order_item表内都有

db.order.aggregate([
    {
        $lookup:{
            from:"order_item",//order要关联order_item表
            localField:"order_id",//要关联的字段  order表本身的 order_id 字段
            foreignField:"order_id",//要关联的字段 关联表的 order_id 字段
            as:"items" //把关联表的数据放到items中
        }
    }
])

结果

{
    "_id": ObjectId("612b399436239d3a8307cfdb"),
    "order_id": "1",
    "uid": 10,
    "trade_no": "111",
    "all_price": 100,
    "all_num": 2,
    "items": [{
        "_id": ObjectId("612b399436239d3a8307cfde"),
        "order_id": "1",
        "title": "商品鼠标1",
        "price": 50,
        "num": 1
    }, {
        "_id": ObjectId("612b399436239d3a8307cfdf"),
        "order_id": "1",
        "title": "商品键盘2",
        "price": 50,
        "num": 1
    }, {
        "_id": ObjectId("612b3deb36239d3a8307cfe2"),
        "order_id": "1",
        "title": "商品键盘3",
        "price": 0,
        "num": 1
    }]
} {
    "_id": ObjectId("612b399436239d3a8307cfdc"),
    "order_id": "2",
    "uid": 7,
    "trade_no": "222",
    "all_price": 90,
    "all_num": 2,
    "items": [{
        "_id": ObjectId("612b399436239d3a8307cfe0"),
        "order_id": "2",
        "title": "牛奶",
        "price": 50,
        "num": 1
    }, {
        "_id": ObjectId("612b399736239d3a8307cfe1"),
        "order_id": "2",
        "title": "酸奶",
        "price": 40,
        "num": 1
    }]
} {
    "_id": ObjectId("612b399436239d3a8307cfdd"),
    "order_id": "3",
    "uid": 9,
    "trade_no": "333",
    "all_price": 20,
    "all_num": 6,
    "items": [{
        "_id": ObjectId("612b3f1136239d3a8307cfe3"),
        "order_id": "3",
        "title": "矿泉水",
        "price": 2,
        "num": 1
    }, {
        "_id": ObjectId("612b3f1236239d3a8307cfe4"),
        "order_id": "3",
        "title": "毛巾",
        "price": 10,
        "num": 1
    }]
}

db.order.aggregate([
    {
        $lookup:{
            from:"order_item",
            localField:"order_id",
            foreignField:"order_id",
            as:"items"
        }
    },
    {
        $match:{"all_price":{$gte:90}} //order表内 筛选出all_price >= 90 的数据
    }
])

精简一下数据

db.order.aggregate([
    {
        $lookup:{
            from:"order_item",
            localField:"order_id",
            foreignField:"order_id",
            as:"items"
        }
    },
    {
        $match:{"all_price":{$gte:90}} 
    },
    {
        $project:{order_id:1,trade_no:1,items:1} //主表值显示这3个字段 记得要把items写进去
    }
])

结果

{
    "_id": ObjectId("612b399436239d3a8307cfdb"),
    "trade_no": "111",
    "items": [{
        "_id": ObjectId("612b399436239d3a8307cfde"),
        "order_id": "1",
        "title": "商品鼠标1",
        "price": 50,
        "num": 1
    }, {
        "_id": ObjectId("612b399436239d3a8307cfdf"),
        "order_id": "1",
        "title": "商品键盘2",
        "price": 50,
        "num": 1
    }, {
        "_id": ObjectId("612b3deb36239d3a8307cfe2"),
        "order_id": "1",
        "title": "商品键盘3",
        "price": 0,
        "num": 1
    }]
} {
    "_id": ObjectId("612b399436239d3a8307cfdc"),
    "trade_no": "222",
    "items": [{
        "_id": ObjectId("612b399436239d3a8307cfe0"),
        "order_id": "2",
        "title": "牛奶",
        "price": 50,
        "num": 1
    }, {
        "_id": ObjectId("612b399736239d3a8307cfe1"),
        "order_id": "2",
        "title": "酸奶",
        "price": 40,
        "num": 1
    }]
}

关系型数据库表（集合）与表（集合）之间的关系 聚合查询

关系型数据库表（集合）与表（集合）之间的关系聚合查询