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实现起来就很简单了,一个函数就搞定了;

知乎同步

MySQL 5.7版本中,没有内置的ROW_NUMBER函数的写法。如果你需要实现类似ROW_NUMBER功能,可以使用变量和子查询来实现。以下是一种实现方式: ```sql CREATE TABLE TZ_DIAG_STAND SELECT * FROM ( SELECT @RN := CASE WHEN @DIAGNOSIS_NAME = DIAGNOSIS_NAME THEN @RN + 1 ELSE 1 END AS RN, @DIAGNOSIS_NAME := DIAGNOSIS_NAME AS DIAG_NAME, MASTER_ID, NUM FROM ( SELECT T1.DIAGNOSIS_NAME, MASTER_ID, COUNT(1) NUM FROM ( SELECT MASTER_ID, DIAGNOSIS_NAME FROM ICD_10 WHERE MASTER_ID IS NOT NULL AND DIAGNOSIS_NAME = '妊娠合并肠炎' ) T1 GROUP BY T1.DIAGNOSIS_NAME, MASTER_ID ORDER BY DIAGNOSIS_NAME, NUM DESC ) A, (SELECT @RN := 0, @DIAGNOSIS_NAME := 0) B ) A WHERE RN = 1; ``` 这个查询使用了变量@RN和@DIAGNOSIS_NAME来模拟ROW_NUMBER函数的功能,并在最外层的WHERE子句中选择RN等于1的记录。这样就可以获得诊断编码出现最多的记录。请注意,这只是一种实现方式,具体的查询语句可能会根据实际情况有所调整。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* *3* [MySQL5.7版本没有row_number函数的写法](https://blog.csdn.net/qq_43278973/article/details/120205035)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT0_1"}}] [.reference_item style="max-width: 100%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

向阳的花儿

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值