mysql与Oracle都可执行的语句实现row number功能

[赵小花啦啦啦啦]

请问，有没有mysql和Oracle都能兼容的语句实现row number功能，在mysql中用if方法可以实现row number功能，但在Oracle 中if不能兼容。请教各位大神有没有一种写法两种数据库都可以实现row number功能的语句，感谢，除max外

[赵小花啦啦啦啦]

引用 3 楼 nayi_224 的回复:

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, t1.val1 --对应order by
)
, tab1 as (
select t1.*, rownum rn from tab0 t1
)
select 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 --对应partition by
) t2
 where t1.part = t2.part
 order by t1.rn
;

drop table test_190507;

mysql不能用with as临时表的

[nayi_224]

引用 4 楼 赵小花啦啦啦啦 的回复:

[quote=引用 3 楼 nayi_224 的回复:]

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, t1.val1 --对应order by
)
, tab1 as (
select t1.*, rownum rn from tab0 t1
)
select 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 --对应partition by
) t2
 where t1.part = t2.part
 order by t1.rn
;

drop table test_190507;

mysql不能用with as临时表的[/quote] 直接在里面套

[卖水果的net]

可以考虑使用mysql 8.0 ，这个有分析函数。

[赵小花啦啦啦啦]

引用 1 楼 卖水果的net 的回复:

可以考虑使用mysql 8.0 ，这个有分析函数。

统一用的是mysql 5.7

[nayi_224]

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, t1.val1 --对应order by
)
, tab1 as (
select t1.*, rownum rn from tab0 t1
)
select 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 --对应partition by
) t2
 where t1.part = t2.part
 order by t1.rn
;

drop table test_190507;

[lhdz_bj]

引用 楼主 赵小花啦啦啦啦 的回复:

请问，有没有mysql和Oracle都能兼容的语句实现row number功能，在mysql中用if方法可以实现row number功能，但在Oracle 中if不能兼容。请教各位大神有没有一种写法两种数据库都可以实现row number功能的语句，感谢，除max外

你的版本，没有。

[lhdz_bj]

引用 3 楼 nayi_224 的回复:

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, t1.val1 --对应order by
)
, tab1 as (
select t1.*, rownum rn from tab0 t1
)
select 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 --对应partition by
) t2
 where t1.part = t2.part
 order by t1.rn
;

drop table test_190507;

这似乎都是oracle语法。

[AHUA1001]

有没有那句话（是语句，不是词。）是汉语和英语，都通用的。即使有，也是可以忽略的概率。
好多时候，同一个系统，可能不同的用户会选择不同的数据库，所以有些时候会考虑，能否把系统中的SQL写出不同数据库都可以支持的。
其实不同的数据库，肯定是有差异的，除非系统超级简单，否则很难把所有语句，都修改成支持不同数据库的。