mysql取分组最新数据

时间:2022-06-18 Tracy苏何人气:0

一、数据表设计

二、查询场景

统计每门课的考试次数、最新一次考试的时间、最新一次考试的录入成绩的老师

1、统计没门课的考试次数

#考试次数统计
select project '科目',count(project) '考试次数' from score a group by project

查询结果：

2、最新一次考试的时间

#考试次数统计	最新一次考试的时间
select project '科目',count(project) '考试次数' ,max(create_time) from score a group by project

查询结果：

3、分组统计最新的录入成绩的老师

当我们分组去查询最新的录入成绩的老师或者分组查询最新一次各科的成绩时确发现数据不是最新的。

SELECT
	a.id,
	a.edit_teacher,
	a.project,
	a.create_time,
	a.score, 
	count(project) '考试次数',
	max(create_time) '最新数据时间'
FROM
	score a 
GROUP BY
	a.project

查询结果：

但是很显然我们需要查询的数据id应该是4、8、12

可以看出分组聚合的时候默认查询的是分组之后的第一条数据，那么我们想要查询最新的数据需要新对我们的数据进行排序

SELECT
	*,
	count( project ) '考试次数',
	max(create_time) '最新数据时间' 
FROM
	(
SELECT
	a.id,
	a.edit_teacher,
	a.project,
	a.create_time,
	a.score 
FROM
	score a 
ORDER BY
	a.id DESC 
	) b 
GROUP BY
	b.project

查询结果：

我们发现数据并不是我们想要的结果，子查询里面的排序失效了

网上查找各种资料发现

子查询生成的临时表（派生表derived table）中使用order by且使其生效，必须满足三个条件：

1、外部查询禁止分组或者聚合

2、外部查询未指定having，HAVING， order by

3、外部查询将派生表或者视图作为from句中唯一指定源

显然我们没有满足，那么如何解决order by失效呢？

我们外部表使用了group by，那么临时表将不会执行filesort操作（即order by会被忽略），所以我们可以在临时表中加上（distinct(a.id)）。

SELECT
	*,
	count( project ) '考试次数' ,
	max(create_time) '最新数据时间' 
FROM
	(
SELECT DISTINCT
	a.id,
	a.edit_teacher,
	a.project,
	a.create_time,
	a.score 
FROM
	score a 
ORDER BY
	a.id DESC 
	) b 
GROUP BY
	b.project

执行结果：

结果正确。

总结

加载全部内容