row_number() over(partition by C_ID order by S_CORE),mysql与oracle的用法

oracle:
建表且插入数据：

create table SCORE
(
  S_ID   NUMBER(11),
  C_ID   NUMBER(11),
  S_CORE NUMBER(11)
)

insert into score (S_ID, C_ID, S_CORE)
values (1, 1, 80);

insert into score (S_ID, C_ID, S_CORE)
values (1, 2, 90);

insert into score (S_ID, C_ID, S_CORE)
values (1, 3, 99);

insert into score (S_ID, C_ID, S_CORE)
values (2, 1, 70);

insert into score (S_ID, C_ID, S_CORE)
values (2, 2, 60);

insert into score (S_ID, C_ID, S_CORE)
values (2, 3, 80);

insert into score (S_ID, C_ID, S_CORE)
values (3, 1, 80);

insert into score (S_ID, C_ID, S_CORE)
values (3, 2, 80);

insert into score (S_ID, C_ID, S_CORE)
values (3, 3, 80);

insert into score (S_ID, C_ID, S_CORE)
values (4, 1, 50);

insert into score (S_ID, C_ID, S_CORE)
values (4, 2, 30);

insert into score (S_ID, C_ID, S_CORE)
values (4, 3, 20);

insert into score (S_ID, C_ID, S_CORE)
values (5, 1, 76);

--分组排序
select t.*, row_number() over(partition by C_ID order by S_CORE) num from score t

看图：

mysql数据库:
        
        
CREATE TABLE score  (
  S_ID int(11) NULL DEFAULT NULL,
  C_ID int(11) NULL DEFAULT NULL,
  S_CORE int(11) NULL DEFAULT NULL
)        
        
        
INSERT INTO score VALUES (1, 1, 80);
INSERT INTO score VALUES (1, 2, 90);
INSERT INTO score VALUES (1, 3, 99);
INSERT INTO score VALUES (2, 1, 70);
INSERT INTO score VALUES (2, 2, 60);
INSERT INTO score VALUES (2, 3, 80);
INSERT INTO score VALUES (3, 1, 80);
INSERT INTO score VALUES (3, 2, 80);
INSERT INTO score VALUES (3, 3, 80);
INSERT INTO score VALUES (4, 1, 50);
INSERT INTO score VALUES (4, 2, 30);
INSERT INTO score VALUES (4, 3, 20);
INSERT INTO score VALUES (5, 1, 76);    
        

--分组排序        
SELECT
    b.*,
IF( @id = c_id, @rank := @rank + 1, @rank := 1 ) AS rownumber1,
    @id := b.c_id 
FROM
    ( SELECT @rownum := 0, @id := NULL, @rank := 0 ) a,
    ( SELECT t.* FROM score t ORDER BY c_id, s_core DESC ) b

看图: