mysql关联分组统计查询

在进行数据统计时，有时我们需要进行关联分组统计，
那我们我们应如何进行关联分组统计呢？
这里记录一个使用示例边后期使用开发。

首先添加测试数据：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(10) NOT NULL DEFAULT 0 COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;
INSERT INTO `user` VALUES (1, '小明', 5), (2, '小红', 8), (3, '小方', 6), (4, '小娜', 7);

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `user_id` int(10) NOT NULL COMMENT '用户ID',
  `course` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程',
  `score` int(10) NOT NULL COMMENT '成绩',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '成绩表' ROW_FORMAT = Dynamic;
INSERT INTO `score` VALUES (1, 1, '语文', 20), (2, 2, '语文', 50), (3, 3, '语文', 25), (4, 1, '数学', 88), (5, 1, '外语', 71), (6, 2, '数学', 51), (7, 2, '外语', 36), (8, 2, '物理', 50);

这里我们以用户成绩表为主，学生表为辅进行查询。

1、首先关联查询，获取每门考试的用户名及成绩：

Select score.*,user.name FROM score INNER JOIN user ON score.user_id=user.id;

2、接着分组查询，获取参加考试的学生：

Select score.id,score.user_id,user.name 
FROM score INNER JOIN user ON score.user_id=user.id 
GROUP BY score.user_id;

3、最后分组统计，获取参加考试每个学生考试数与平均分：

Select score.id,score.user_id,user.name,COUNT(score.user_id) AS count,CAST(AVG(score) AS DECIMAL(10,2)) AS avg 
FROM score INNER JOIN user ON score.user_id=user.id 
GROUP BY score.user_id;

4、在上面我们只计算了参与考试的，下面我们将没有参与考试的也添加进去统计

Select user.id,user.name,score.user_id,COUNT(score.user_id) AS count,CAST(AVG(score) AS DECIMAL(10,2)) AS avg 
FROM user LEFT JOIN score ON user.id=score.user_id 
GROUP BY score.user_id ORDER BY count DESC;

说明 CAST(AVG(score) AS DECIMAL(10,2)) AS avg 计算平均数并保留两位小数

5、统计查询，计算某年每月销售额总和：

SELECT DATE_FORMAT(date,'%Y-%m') AS time,sum(total_price) AS money FROM order WHERE date_format(date,'%Y-%m-%d') between '2020-01-01' and '2020-12-31' GROUP BY time;