mysql获取分组中的指定数据(附四大排序函数说明)

一.背景

：
举个例子，现有两张表分别是老师和学生，一个老师有多个学生,学生表内有老师ID做关联。

查询所有老师及对应的学生信息：

现在需求是查询每个老师及其手下分数最高的学生信息，我们应该如何书写语句呢？几种实现方式我们逐一演示

二.解决方案

1.先排序后分组方式

直接运行，结果报错。不要慌，出错原因是mysql版本问题，mysql5.7之后的版本为了语法的严谨（据说是为了对标Oracle）默认开启了only_full_group_by，顾名思义就是只支持分组覆盖查询，听着有点懵？索引覆盖听说过吧？这里和覆盖索引类似，就是查询的字段要和分组的字段相同，也就是只能select 分组字段或者聚合函数。

怎么解决呢？推荐一劳永逸的方式，修改配置文件：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

以上设置对新建数据（全局）生效，对于已经存在的数据，要在对应库表中执行：

set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

降序排序后分组结果仍然不正确，我们对语句进行分解。首先执行排序语句

因为在子查询排序后再进行分组查询时，系统调用查询优化器，放弃了子查询的排序改用默认的按ID排序，这个过程可以手动演示：

这时我们可以在子查询语句中的排序后加上Limit，做出限制。系统查询优化器识别到子查询中的在保留limit的同时也保留了子查询的排序规则。

	SELECT * FROM  (SELECT * from  student ORDER BY score desc  limit 1000) as tt GROUP BY teacherId;

最终语句：

WITH st
as (
	SELECT * FROM  (SELECT * from  student ORDER BY score desc  limit 1000) as tt GROUP BY teacherId
)

SELECT t.*,st.*
from teacher t
left JOIN st
on t.id=st.teacherId;

2.利用rank() over…（推荐）

 WITH st
as (
	select *,RANK() over(PARTITION by teacherId ORDER BY score DESC ) as 组内分数排名 FROM student
)

SELECT t.*,st.*
from teacher t
left JOIN st
on t.id=st.teacherId
WHERE 组内分数排名=1;

3.1 mysql四大排名函数

四大排名函数分别是：RANK()、DENSE_RANK()、ROW_NUMBER()、NTILE(N)。以下是同时使用的效果，我们从中可以看出差异

（1）排序条件下的排名

（2）分区排序条件下的排名

PARTITION BY用来分区，与GROUP BY类似，但是分区不像分组会对结果进行汇总，说通俗点，分组是将数据分组后返回将每组中的一条数据作为代表返回，而分区是全部返回。