牛客SQL进阶篇SQL130题，sum/count和avg区别

以下数据是"提交"数据库里的，不是"自测"数据库

user_info表

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	3100	7	算法	2020-01-01 10:00:00
2	1002	牛客2号	2100	6	算法	2020-01-01 10:00:00
3	1003	牛客3号	1500	5	算法	2020-01-01 10:00:00
4	1004	牛客4号	1100	4	算法	2020-01-01 10:00:00
5	1005	牛客5号	1600	6	C++	2020-01-01 10:00:00
6	1006	牛客6号	3000	6	C++	2020-01-01 10:00:00

examination_info表

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-09-01 06:00:00
2	9002	SQL	easy	60	2021-09-01 06:00:00
3	9003	算法	medium	80	2020-08-02 10:00:00

exam_record表

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2021-09-01 09:01:01	2021-09-01 09:41:01	70
2	1002	9003	2021-09-01 12:01:01	2021-09-01 12:21:01	60
3	1002	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	70
4	1002	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	80
5	1002	9003	2021-08-01 12:01:01	2021-08-01 12:21:01	60
6	1002	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	70
7	1002	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	85
8	1002	9002	2021-09-01 12:01:01	(NULL)	(NULL)
9	1003	9003	2021-09-07 10:01:01	2021-09-07 10:31:01	86
10	1003	9003	2021-09-08 12:01:01	2021-09-08 12:11:01	40
11	1003	9001	2021-09-01 13:01:01	2021-09-01 13:41:01	70
12	1003	9002	2021-09-01 14:01:01	(NULL)	(NULL)
13	1003	9003	2021-09-08 15:01:01	(NULL)	(NULL)
14	1005	9001	2021-09-01 12:01:01	2021-09-01 12:31:01	90
15	1005	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	88
16	1006	9002	2021-09-01 12:11:01	2021-09-01 12:31:01	89

SQL类别的试卷，试卷ID为9001、9002，发布当天（2021-09-01）做题且五级以上的用户有1001、1002，9001试卷的得分[70、80、85、90],9002试卷的得分[70、70、null、88、89]

请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序

答案：

select r.exam_id,

count(distinct r.uid) as uv,

round(avg(score),1) as avg_score

from exam_record r

join user_info u on r.uid=u.uid

join examination_info i on r.exam_id=i.exam_id

where level>5 and tag='SQL' and date_format(submit_time,'%Y%m%d')

=date_format(release_time,'%Y%m%d')

group by r.exam_id

order by uv desc,avg_score;

计算avg_score的时候，我用的sum(score)/count(uid)结果不对，9001平均分是一样的，9002平均分不同，原因在于9002有一份没分数，分母+1，avg函数自动忽略null，也是sum/count和avg的区别之一，这么简单的问题浪费了好长时间，失策失策