☆【嚣张恒少】mysql实现oracle row_number窗口函数

– oracle：
测试数据：
create table test_zh(id varchar2(10),time date,a1 varchar2(10));
insert into test_zh select 1,sysdate,‘a’ from dual;
insert into test_zh select 2,sysdate,‘a’ from dual;
insert into test_zh select 5,sysdate,‘b’ from dual;
insert into test_zh select 1,sysdate,‘b’ from dual;
insert into test_zh select 1,sysdate,‘b’ from dual;
insert into test_zh select 1,sysdate,‘c’ from dual;
insert into test_zh select 1,sysdate,‘d’ from dual;

sql主体：
Select Id,time,a1,Row_Number() Over(Partition By a1 Order By time Desc) Px From test_zh;

1 2019/9/29 16:15:33 a 1
2 2019/9/29 16:15:36 a 2
5 2019/9/29 16:15:40 b 1
1 2019/9/29 16:15:43 b 2
1 2019/9/29 16:15:45 b 3
1 2019/9/29 16:15:48 c 1
1 2019/9/29 16:15:50 d 1

– mysql：
测试数据：
create table test_zh(id varchar(10),time datetime,a1 varchar(10));
insert into test_zh select 1,sysdate(),‘a’;
insert into test_zh select 2,sysdate(),‘a’;
insert into test_zh select 5,sysdate(),‘b’;
insert into test_zh select 1,sysdate(),‘b’;
insert into test_zh select 1,sysdate(),‘b’;
insert into test_zh select 1,sysdate(),‘c’;
insert into test_zh select 1,sysdate(),‘d’;
sql主体：
SELECT id,time,a1,@row_number := CASE
WHEN @i = a1 THEN @row_number + 1
ELSE 1
end AS num,@i:=a1
FROM test_zh
ORDER BY a1,time Desc;
±-----±--------------------±-----±-----±-------+
| id | time | a1 | num | @i:=a1 |
±-----±--------------------±-----±-----±-------+
| 2 | 2019-09-29 16:12:19 | a | 1 | a |
| 1 | 2019-09-29 16:12:15 | a | 2 | a |
| 1 | 2019-09-29 16:12:30 | b | 1 | b |
| 1 | 2019-09-29 16:12:27 | b | 2 | b |
| 5 | 2019-09-29 16:12:22 | b | 3 | b |
| 1 | 2019-09-29 16:12:34 | c | 1 | c |
| 1 | 2019-09-29 16:12:37 | d | 1 | d |
±-----±--------------------±-----±-----±-------+
7 rows in set (0.00 sec)