oracle row_number()的用法

GPS平台、网站建设、软件开发、系统运维，找森大网络科技！
https://cnsendnet.taobao.com
来自森大科技官方博客
http://www.cnsendblog.com/index.php/?p=2213

oracle row_number() – 梦里故乡 – 博客园

要求查询每个用户对应的最大样品信息，忽然想到ms sql提供过 row_number() over(partition by 列 order by 列 desc），那么oracle可能也存在，

我的表结构如下：

create table NEOGOODSRULE
(
  ID          NUMBER(22) not null,
  PERSONALID  NVARCHAR2(50),
  CT_SMP_TYPE NVARCHAR2(100)
)
tablespace VGSM
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

create table NEOGOODSRULE

(

ID NUMBER(22) not null,

PERSONALID NVARCHAR2(50),

CT_SMP_TYPE NVARCHAR2(100)

)

tablespace VGSM

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

数据如下：

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2270, 'JYZ', '原料');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2271, 'JYZ', '辅料');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2359, 'SYSTEM', '包材(内)');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2360, 'SYSTEM', '包材(外)');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2361, 'SYSTEM', '原料');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2362, 'SYSTEM', '成品');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2363, 'SYSTEM', '稳定性(加速)');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2364, 'SYSTEM', '稳定性(长期)');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2365, 'SYSTEM', '辅料');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2354, 'LY', '成品');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2355, 'LY', '原料');