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  );

資料如下：

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', '原料');

ROW_NUMBER（）文法如下：

1、row_number() over(order by column asc)先對列column按照升序，再為每條記錄返回一個序號：

select personalid,row_number() over(order by personalid asc) rn from  neogoodsrule

2、row_number() over(partition by column1 order by column2 asc) 先按照column1分組，再對分組後的資料進行以column2升序排列

select personalid,ct_smp_type,row_number() over(partition by personalid order by ct_smp_type asc) rn from neogoodsrule 

 

由此，開始所提的需求sql代碼如下

select * from (select personalid,ct_smp_type,row_number() over(partition by personalid order by ct_smp_type asc) rn from neogoodsrule )
where rn=1