Mysql实现SQL Row_Number函数，对数据进行分组排序

在标准SQL中，如果使用Group by，select字段中只能返回group by的字段或者其他字段的聚合（Min,Max等）；

如果要在select之后返回其他的数值，不能使用group by分组，应该使用OVER窗口函数；

相当于把整个结果集分割为数个小结果集，可以在每个小结果集中进行取值，排序。

最常用的需求是“在窗口函数中进行排序，然后根据排序结果筛选获取”；

在SQL中，可以使用ROW_NUMBER OVER(partition by order by )实现；

在Mysql中没有row_number函数，可以使用case when变量实现；

下边主要讲一下使用case when变量实现窗口排序的方法；

测试数据如下

create table buy (name1 varchar(10) ,sex varchar(2),datet date,products varchar(20));
insert into buy 
values('小红','女','2020-04-01','苹果'),('小红','女','2020-04-02','香蕉'),('小明','男','2020-04-01','橘子'),('小明','男','2020-04-02','火龙果')
select * from buy order by datet desc

create table buy2 (name1 varchar(10),age int);
insert into buy2
values('小红',27),('小明',28)
select * from buy2

需要返回，每个人最近购买的一笔产品信息，包含人员详细信息

实现代码如下：

SET @row_number:=0,@name1:=null;
with cts as (
select 
    b1.*,
    @row_number:=CASE WHEN @name1 <> b1.name1 THEN 1 else @row_number + 1 END AS num,
    @name1:=b1.name1
from (
select b1.*,b2.age from buy as b1
left join buy2 as b2 on b1.name1=b2.name1
) as b1
order by b1.name1,datet desc)
select c.* from cts as c wherE NUM=1
order by name1,datet desc

原理：

使用变量@name1（初始值为null），如果下一条name1仍旧相同，则rownum加1，如果不同，则相当于重新开窗口计算，rownum为1；

需要注意的是：

1. 对变量的赋值要放在排序后@name1:=b1.name1，可以看上图中@name1的值刚开始是null
2. order by 中要注意排序的顺序，与rownumber生成有关
3. 经过我反复的测试，我发现case when使用的后边不支持join表，最好将表join好之后，内连接在case when的from后边（from后为一张表，提前join好）

---

用sql实现起来就很简单了，一个函数就搞定了；

知乎同步