分组查询

GROUP 分组查询

>[danger] 分组查询，顾名思义就是按照组区分开，进行查询，比如按照性别分组 > 注意！！！ group by 后面分组的字段不能随便加，比如 group by 'sex' 那么，查询字段必须为 sex

按照性别分组查询全部数学成绩的平均分

const { Sequelize } = app;
// 按照性别分组查询全班数学成绩的平均分
const ret = await Student.findAll({
    // 查询字段要和分组字段一致！！！！
    attributes:['sex', [ Sequelize.fn('AVG', Sequelize.col('math')), 'math_avg' ] ],
    // 分组字段
    group: ['sex']
})

SELECT `sex`, AVG(`math`)  FROM `student`   GROUP BY `sex`;

[
    {
        "sex": "女",
        "math_avg": "85.4286"
    },
    {
        "sex": "男",
        "math_avg": "78.0000"
    }
]

按照性别分组，分别查询全班同学数学成绩平均分和总人数

// 按照性别分组，分别查询全班同学数学成绩平均分和总人数
const ret = await Student.findAll( {
    attributes:[
        'sex',
        [Sequelize.fn('AVG', Sequelize.col('math')), 'match_avg'],
        [Sequelize.fn('COUNT', Sequelize.col('id')), 'total_student']
    ],
    group: ['sex']
} )

SELECT `sex`, AVG(`math`) , COUNT(`id`)  FROM `student` GROUP BY `sex`;

[
    {
        "sex": "女",
        "match_avg": "85.4286",
        "total_student": 7
    },
    {
        "sex": "男",
        "match_avg": "78.0000",
        "total_student": 5
    }
]

按照地区分组，分别统计每个地区数学成绩平均分 和 总人数，分数低于70分的同学不参与分组

// 按照地区分组，分别统计每个地区数学成绩平均分 和 总人数，分数低于70分的同学不参与分组
const ret = await Student.findAll( {
    attributes:[
        'address',
        [ Sequelize.fn('AVG', Sequelize.col('math')), 'math_avg' ],
        [ Sequelize.fn('COUNT', Sequelize.col('id')), 'total_student' ]
    ],
    where: {
        math: {
            [Op.gt]:70
        }
    },
    group: ['address']
} )

SELECT `address`, AVG(`math`) , COUNT(`id`)  FROM `student` WHERE  `math` > 70 GROUP BY `address`;

[
    {
        "address": "四川",
        "math_avg": "84.0000",
        "total_student": 2
    },
    {
        "address": "昆明",
        "math_avg": "90.0000",
        "total_student": 1
    },
    {
        "address": "潭州",
        "math_avg": "92.0000",
        "total_student": 1
    },
    {
        "address": "西凉",
        "math_avg": "82.0000",
        "total_student": 2
    },
    {
        "address": "贵州",
        "math_avg": "86.2500",
        "total_student": 4
    }
]

按照地区分组，分别统计每个地区学生的数学平均分，分数低于70分的同学不参与分组，并且参与分组的人数不能少于2人

>[danger] 注意！！！where 和 having 的区别？ > where 在分组之前进行限定，如果不满足条件，则不参与分组 > having 在分组之后进行限定，如果不满足结果，则不会被查询出来 > where 后不可以带上聚合函数判断，having 后可以带上聚合函数进行判断

await Student.findAll( {
    attributes:[
        'address',
        [Sequelize.fn('AVG', Sequelize.col('math')), 'math'],
        [Sequelize.fn('COUNT', Sequelize.col('id')), 'total']
    ],
    where: {
        math: {
            [Op.gt]:70
        }
    },
    group: 'address',
    having: {
        total:{
            [Op.gt]:2
        }
    }
})
SELECT `address`, AVG(`math`),COUNT(`id`) as total FROM `student` 
    WHERE `student` > 70 
    GROUP BY `address` 
    HAVING `total` > 2;