Mysql分组排序后取每组第一条数据的2种实现方式

前言

mysql 5.7.x版本，默认开启了only_full_group_by模式，但是开启这个模式后
有的sql语句就有可能报错

Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘×××’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

含义 sql中select后面的字段必须出现在group by后面，或者被聚合函数包裹，不然会抛出上面的错误 如以下sql
例如：

select a , b from table_a  group by a;

此时 查询的字段b 不在 group by后面 所以会报错

此时 如果按照a字段分组之后的其他字段值都一样 则 可以使用聚合函数max() , min() 或者 any_value()函数

select a , max(b) from table_a  group by a;
select a , any_value(b) from table_a  group by a;  //这个函数会取分组 组内的第一条数据

如果我们分组 组内的数据各不相同 又想取某个特新排序下的 最新值 则可以 有如下三种方式

有如下数据：

需要查询姓名相同的人员中 存款最高的 人员信息

方式一、

-- 查询姓名相同的人员中 存款最高的 人员信息

SELECT any_value(id),NAME,any_value(age),any_value(money) FROM 
(SELECT * FROM `t_test_group` ORDER BY money DESC LIMIT 2000000000) t  GROUP BY t.name;

但是这样有个弊端 就是每次查询 都会将数据库内的数据全部重新排序一遍 如果数据库内的数据过多 则可能会导致查询过慢

方式二、

使用窗口函数（Window Function）和 ROW_NUMBER() 函数来实现

-- 查询姓名相同的人员中 存款最高的 人员信息
SELECT id,NAME,age,money
FROM (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY money DESC) AS row_num
    FROM t_test_group
) AS ranked
WHERE row_num = 1;

窗口函数

窗口函数（Window Function）是一类用于在查询结果集内执行计算并返回有关窗口（一组相关的行）的信息的函数。这些函数通常与 OVER 子句一起使用，该子句定义了窗口的范围，即哪些行应该被包括在计算中。

窗口函数的一个主要优势是它们允许在结果集中的行之间执行计算，而不仅仅是对整个结果集进行聚合。这对于在特定分组内执行排名、计算累积总和、计算移动平均等任务非常有用。

在SQL中，常见的窗口函数包括：

ROW_NUMBER(): 为结果集中的每一行分配一个唯一的整数值，通常用于执行排序和分组内的排名。

RANK(): 为结果集中的每一行分配一个排名值，如果有相等的值，则它们将共享相同的排名，而下一个排名将跳过相等的值。

DENSE_RANK(): 与RANK()类似，但不会跳过相等的值，即相等的值会共享相同的密集排名。

SUM(), AVG(), COUNT(), MAX(), MIN(): 这些聚合函数可以与窗口函数一起使用，以计算在窗口内的汇总、平均值、计数、最大值或最小值。

LEAD() 和 LAG(): 用于访问窗口内当前行之前或之后的行的值。

窗口函数的语法通常如下:

<窗口函数> OVER (
    [PARTITION BY <分区列>]
    ORDER BY <排序列>
    [ROWS <窗口规格>]
)

PARTITION BY: 按照指定的列对结果集进行分区，使窗口函数在每个分区内独立计算。
ORDER BY: 指定窗口函数计算时的排序规则。
ROWS: 定义窗口的边界，可以是范围（RANGE）或行数（ROWS）。
通过使用窗口函数，可以在查询中更灵活地执行各种分析和聚合操作，特别是在需要在特定分组内执行计算的情况下。