场景:mysql如何按多列进行写入标签列,MySQL8.0版本才有ROW_NUMBER() OVER (<partition_definition> <order_definition>) 这个函数,但又想实现同样的功能,5.x需要另一种写法。
建表语句
create table test(A varchar(50),
B varchar(50),
C varchar(50),
D varchar(50),
E varchar(50)
);
插入数据
insert into test values
('v1','2022/10/11','22','33','44'),
('v1','2022/10/11','45','23','54'),
('v1','2022/10/13','48','35','56'),
('v2','2022/10/14','52','38','75'),
('v2','2022/10/15','64','49','32');
原MySQL结构 SQL版本5.x
期望的sql结构:
解决方案:
select
*
from (
select
a.A,
a.B,
a.C,
a.D,
a.E,
@F := case when @B = a.B then @F + 1
else 1
end as rank_no,
@B := a.B
from (select * from test order by B,D desc) a,
(select @F := 0,@B :='') b
) a;
查询结果