Mysql and Oracle are executable statements for the row number-CodePudding

Excuse me, is there a mysql and Oracle can be compatible with statements for the row number function, using the if in your mysql method can realize the row number, but in Oracle if not compatible, consult everybody a great god is there a written both database can implement the row number function statements, thank you, in addition to the Max

CodePudding user response:

Can consider to use mysql 8.0, this analysis function,

CodePudding user response:

reference 1st floor selling fruit net reply:

can consider to use mysql 8.0, this analysis function,

Unified using mysql 5.7

CodePudding user response:

 create table test_190507 (part VARCHAR (50), val1 NUMERIC, val2 NUMERIC); 

Insert into test_190507 (part, val1, val2) values (' a ', 1, 4); 
Insert into test_190507 (part, val1, val2) values (' a ', 2, 3); 
Insert into test_190507 (part, val1, val2) values (' a ', 3, 2); 
Insert into test_190507 (part, val1, val2) values (' a ', 4, 1); 
Insert into test_190507 (part, val1, val2) values (' b ', 1, 4); 
Insert into test_190507 (part, val1, val2) values (' b ', 1, 3); 
Insert into test_190507 (part, val1, val2) values (' b ', 2, 2); 
Insert into test_190507 (part, val1, val2) values (' c ', 1, 3); 
Insert into test_190507 (part, val1, val2) values (' c ', 2, 2); 

With tab0 as (
Select * from test_190507 t1 
Order by t1. Part, corresponding order by t1. Val1 -- -- 
) 
, tab1 as (
Select a t1. *, rownum rn from tab0 t1 
) 
Select a t1. *, t1. Rn - t2. Mn + 1 row_number from tab1 t1, 
(select t1. Part, count (1) ct, min (t1) rn) mn from tab1 t1 
Group by t1. Part -- -- the corresponding partition by 
T2) 
Where a t1. Part=t2. Part 
The order by t1. Rn 
; 

Drop table test_190507;

CodePudding user response:

The

reference 3 floor nayi_224 response:

 create table test_190507 (part VARCHAR (50), val1 NUMERIC, val2 NUMERIC); 

Insert into test_190507 (part, val1, val2) values (' a ', 1, 4); 
Insert into test_190507 (part, val1, val2) values (' a ', 2, 3); 
Insert into test_190507 (part, val1, val2) values (' a ', 3, 2); 
Insert into test_190507 (part, val1, val2) values (' a ', 4, 1); 
Insert into test_190507 (part, val1, val2) values (' b ', 1, 4); 
Insert into test_190507 (part, val1, val2) values (' b ', 1, 3); 
Insert into test_190507 (part, val1, val2) values (' b ', 2, 2); 
Insert into test_190507 (part, val1, val2) values (' c ', 1, 3); 
Insert into test_190507 (part, val1, val2) values (' c ', 2, 2); 

With tab0 as (
Select * from test_190507 t1 
Order by t1. Part, corresponding order by t1. Val1 -- -- 
) 
, tab1 as (
Select a t1. *, rownum rn from tab0 t1 
) 
Select a t1. *, t1. Rn - t2. Mn + 1 row_number from tab1 t1, 
(select t1. Part, count (1) ct, min (t1) rn) mn from tab1 t1 
Group by t1. Part -- -- the corresponding partition by 
T2) 
Where a t1. Part=t2. Part 
The order by t1. Rn 
; 

Drop table test_190507;

Mysql cannot be used with the as a temporary table

CodePudding user response:

reference xiao-hua zhao, 4/f, la la la la response:

Quote: refer to the third floor nayi_224 response:

 create table test_190507 (part VARCHAR (50), val1 NUMERIC, val2 NUMERIC); 

Insert into test_190507 (part, val1, val2) values (' a ', 1, 4); 
Insert into test_190507 (part, val1, val2) values (' a ', 2, 3); 
Insert into test_190507 (part, val1, val2) values (' a ', 3, 2); 
Insert into test_190507 (part, val1, val2) values (' a ', 4, 1); 
Insert into test_190507 (part, val1, val2) values (' b ', 1, 4); 
Insert into test_190507 (part, val1, val2) values (' b ', 1, 3); 
Insert into test_190507 (part, val1, val2) values (' b ', 2, 2); 
Insert into test_190507 (part, val1, val2) values (' c ', 1, 3); 
Insert into test_190507 (part, val1, val2) values (' c ', 2, 2); 

With tab0 as (
Select * from test_190507 t1 
Order by t1. Part, corresponding order by t1. Val1 -- -- 
) 
, tab1 as (
Select a t1. *, rownum rn from tab0 t1 
) 
Select a t1. *, t1. Rn - t2. Mn + 1 row_number from tab1 t1, 
(select t1. Part, count (1) ct, min (t1) rn) mn from tab1 t1 
Group by t1. Part -- -- the corresponding partition by 
T2) 
Where a t1. Part=t2. Part 
The order by t1. Rn 
; 

Drop table test_190507;

Mysql cannot be used with the as temporary table

Directly set in it

CodePudding user response:

refer to the original poster xiao-hua zhao la la la la response:

excuse me, is there a mysql and Oracle can be compatible with statements for the row number function, using the if in your mysql method can realize the row number, but in Oracle if not compatible, consult everybody a great god is there a written both database can implement the row number function statements, thank you, in addition to the Max

Your version, no,

CodePudding user response:

The

reference 3 floor nayi_224 response:

 create table test_190507 (part VARCHAR (50), val1 NUMERIC, val2 NUMERIC); 

Insert into test_190507 (part, val1, val2) values (' a ', 1, 4); 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull