sql分组查询group by结合count,sum统计语句的实现(附带sql详细分析步骤)

数据库 专栏收录该内容
15 篇文章 2 订阅

日常写代码经常会遇到数据统计的业务场景,分组查询 group by 结合 count 和 sum 的复杂语句写起来容易令人头大,在这里分享两个比较复杂的统计场景,提供详细分析思路和最终sql语句,希望能给大家带来帮助。

场景1:统计每个学生的加减分次数和总分

库表结构如下所示:

学生分数表:student_score

字段名含义字段类型备注
id自增编号bigint
student_name学生姓名varchar
score分数double
sort分数类型int1-加分;2-减分;
is_delete删除标志位int默认为0;

一上来不着急写 sql,先来分析一下:

因为统计的是每个学生的分数,所以根据学生名称 student_name 进行 group by 分组查询。

需要获取的字段包括 学生名称 student_name,加分次数 add_count,扣分次数 sub_count,总加分 add_score,总扣分 sub_score,其中,学生名称不需要计算,因此只需处理次数和分数

我们知道,count() 主要用于求行的个数累计,所以当分数类型 sort 为 1,则增加加分次数,sort 为 2,则增加扣分次数;而 sum() 用于求和累加,因而使用 sum() 来计算总分,分数类型 sort 为 1,则加分,sort 为 2,则减分。

有了清晰的思路,sql就不难写了:

SELECT
	student_name,
	count(sort = 1 OR NULL) add_count,
	count(sort = 2 OR NULL) sub_count,
	sum(IF(sort = 1, score, 0)) add_score,
	sum(IF(sort = 2, score, 0)) sub_score
FROM
	student_score
WHERE
	is_delete = 0
GROUP BY
	student_name

原始表数据:
在这里插入图片描述
统计结果:
在这里插入图片描述
这里的次数和分数的条件判断是通过 if 语句来实现的,我们也可以通过 case when 语句来实现:

SELECT
	student_name,
	count(case when sort = 1 then 1 else null end) add_count,
	count(case when sort = 2 then 1 else null end) sub_count,
	sum(case when sort = 1 then score else 0 end) add_score,
	sum(case when sort = 2 then score else 0 end) sub_score
FROM
	student_score
WHERE
	is_delete = 0
GROUP BY
	student_name

场景2:按照权重统计每个学生的体测成绩

体育测试中我们要根据不同的指标对学生进行打分,目前分为5项指标:800米,50米,立定跳远,仰卧起坐和坐位体前屈。总分为100分,每项的分数权重占比不一样,如下表所示:

项目分数占比(%)
800米30
50米15
立定跳远20
仰卧起坐15
坐位体前屈20

其中立定跳远需要测试3次,每次都进行打分,需要根据这3次的得分计算出平均分作为最终得分,未来其他指标也可能采用这种方式进行打分。

需求明确了,先来设计数据库表,由于每项指标的权重是固定的,可以采用单独一个表来存储指标权重:

指标权重表:index_score

字段名含义字段类型备注
id自增编号bigint
index_code指标编号varchar
index_name指标名称varchar
share指标权重int
is_delete删除标志位int默认为0;

由于库表中需要保存全量详细的体测记录,所以需要记录每个学生的指标和对应的评分。这里通过指标编号进行关联,结果记录表如下所示:

结果记录表:score_record

字段名含义字段类型备注
id自增编号bigint
student_name学生名称varchar
index_code指标编号varchar
score对应指标得分double
is_delete删除标志位int默认为0;

我们需要通过一个sql关联 指标权重表结果记录表 来获取每个学生的姓名和总得分。这个场景比较复杂,一步一步来分析:

首先先来处理最复杂的立定跳远项,先计算每个学生的3次得分的平均分,由于未来其他指标也可能采用这种方式进行打分,那么我们就要根据学生姓名和指标编号共同进行 group by 分组后再获取平均值:

SELECT
	student_name,
	index_code,
	round(avg(score), 1) index_avg
FROM
	score_record r
WHERE
	r.is_delete = 0
GROUP BY
	student_name, index_code

原始表数据:
在这里插入图片描述
统计结果:
在这里插入图片描述
统计出每个学生的各项指标得分就完成了第一步,第二步需要根据各项指标的权重结合得分计算总分。这就需要结合指标权重表来获取每项指标的权重占比 share,再乘以第一步计算出的平均值 index_avg 后,最后使用 sum() 来计算出总分。

当然这里需要根据学生姓名来进行分组,对于计算出的总分还要使用 round() 函数保留两位小数,最终 sql 如下:

SELECT
	round(sum(s.share * t.index_avg) / 100, 2) score, t.student_name
FROM
	(
		SELECT
			student_name,
			index_code,
			round(avg(score), 1) index_avg
		FROM
			score_record r
		WHERE
			r.is_delete = 0
		GROUP BY
			student_name,index_code
	) t
LEFT JOIN index_score s ON t.index_code = s.index_code
GROUP BY
	t.student_name

统计结果:
在这里插入图片描述
这样一个看起来有点复杂的 sql,采用从里到外逐步分析的方式,也就可以轻松写出来了。

  • 3
    点赞
  • 2
    评论
  • 3
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 撸撸猫 设计师:马嘣嘣 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值