mysql如何按多列进行写入标签列

源mysql结构 SQL版本5.x
A B C D E
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

期望的sql结构：
A B C D E F
v1 2022/10/11 22 33 44 1
v1 2022/10/11 45 23 54 2
v1 2022/10/13 48 35 56 1
v2 2022/10/14 52 38 75 1
v2 2022/10/15 64 49 32 1

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;

作为参考

F列的1,2是B列相同根据D列排序来的吗

select
 *
from (
    select 
        @F := case when @B = a.B then @F + 1
               else 1
        end as F,
        @B := a.B,
        a.B,
        a.A,  
        a.C,
        a.D,
        a.E,
    from (select * from table order by B,D desc) a,
    (select @F = 0,@B='') b    
) a

WITH T1 AS (
SELECT 'V1' A ,'20221011' B,22 C ,33 D ,44 E UNION 
SELECT 'V1' A ,'20221011' B,45 C ,23 D ,54 E UNION 
SELECT 'V1' A ,'20221013' B,48 C ,35 D ,56 E UNION 
SELECT 'V2' A ,'20221014' B,52 C ,38 D ,75 E UNION 
SELECT 'V2' A ,'20221015' B,64 C ,49 D ,32 E )  --  测试数据 
SELECT *,ROW_NUMBER()OVER(PARTITION BY A,B ORDER BY A,B) RO 
FROM T1