row_number() over()和rownum最大的区别在于前者可以进行pratition by. 例如: with t as (select 1 id, 10 c1 from dual union all select 1 id, 20 c1 from dual union all select 2 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual) select t.*, row_number() over(partition by id order by c1) from t; 其实这个才是row_number()over最大的作用
row_number() over()和rownum最大的区别在于前者可以进行pratition by. 例如: with t as (select 1 id, 10 c1 from dual union all select 1 id, 20 c1 from dual union all select 2 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual) select t.*, row_number() over(partition by id order by c1) from t; 其实这个才是row_number()over最大的作用 顶一个、赞一个。。这个是个很有代表性的例子。
row_number() over()和rownum最大的区别在于前者可以进行pratition by. 例如: with t as (select 1 id, 10 c1 from dual union all select 1 id, 20 c1 from dual union all select 2 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual) select t.*, row_number() over(partition by id order by c1) from t; 其实这个才是row_number()over最大的作用 是不是也可以这样写 select t.* from t group by id order by c1
row_number() over()和rownum最大的区别在于前者可以进行pratition by. 例如: with t as (select 1 id, 10 c1 from dual union all select 1 id, 20 c1 from dual union all select 2 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual) select t.*, row_number() over(partition by id order by c1) from t; 其实这个才是row_number()over最大的作用 是不是也可以这样写 select t.* from t group by id order by c1 不一样。。row_number()over实现了按组单独排序并分配序列号。
row_number() over()和rownum最大的区别在于前者可以进行pratition by. 例如: with t as (select 1 id, 10 c1 from dual union all select 1 id, 20 c1 from dual union all select 2 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual union all select 4 id, 10 c1 from dual) select t.*, row_number() over(partition by id order by c1) from t; 其实这个才是row_number()over最大的作用 是不是也可以这样写 select t.* from t group by id order by c1 不一样的,你这个SQL是对全部结果按C1排序。而ROW_NUMBER()OVER()是在每个ID中进行排序。
例如:
with t as
(select 1 id, 10 c1
from dual
union all
select 1 id, 20 c1
from dual
union all
select 2 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual)
select t.*, row_number() over(partition by id order by c1) from t;
其实这个才是row_number()over最大的作用
例如:
with t as
(select 1 id, 10 c1
from dual
union all
select 1 id, 20 c1
from dual
union all
select 2 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual)
select t.*, row_number() over(partition by id order by c1) from t;
其实这个才是row_number()over最大的作用
顶一个、赞一个。。这个是个很有代表性的例子。
例如:
with t as
(select 1 id, 10 c1
from dual
union all
select 1 id, 20 c1
from dual
union all
select 2 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual)
select t.*, row_number() over(partition by id order by c1) from t;
其实这个才是row_number()over最大的作用
是不是也可以这样写
select t.* from t group by id order by c1
例如:
with t as
(select 1 id, 10 c1
from dual
union all
select 1 id, 20 c1
from dual
union all
select 2 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual)
select t.*, row_number() over(partition by id order by c1) from t;
其实这个才是row_number()over最大的作用
是不是也可以这样写
select t.* from t group by id order by c1
不一样。。row_number()over实现了按组单独排序并分配序列号。
例如:
with t as
(select 1 id, 10 c1
from dual
union all
select 1 id, 20 c1
from dual
union all
select 2 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual
union all
select 4 id, 10 c1
from dual)
select t.*, row_number() over(partition by id order by c1) from t;
其实这个才是row_number()over最大的作用
是不是也可以这样写
select t.* from t group by id order by c1
不一样的,你这个SQL是对全部结果按C1排序。而ROW_NUMBER()OVER()是在每个ID中进行排序。