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

 

使用row_number()分頁函數取代group by。例如：語句1:
select col1,col2 from t where col1 in (select col1 from t group by col1 having count(*) <=2 ) order

by order col2;
語句1可以用下面的語句取代：
語句2:
select col1,col2 from (select *,row_number() over(partition by col1 order by col2) as p_group from

t) where p_group<=2 ;

ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
表示根據COL1分組，在分組內部根據 COL2排序。而這個值就表示每組內部排序後的順序編號（組內連續的唯一的

）。

----------------------------------------------------------------------------------

   select a,b,c,sum(c)over(partition by a) from t2                
   得到結果：
   A   B   C        SUM(C)OVER(PARTITIONBYA)      
   -- -- ------- ------------------------ 
   h   b   3        3                        
   m   a   2        4                        
   m   a   2        4                        
   n   a   3        6                        
   n   b   2        6                        
   n   b   1        6                        
   x   b   3        9                        
   x   b   2        9                        
   x   b   4        9                        
  
   如果用sum，group by 則只能得到
   A   SUM(C)                            
   -- ---------------------- 
   h   3                      
   m   4                      
   n   6                      
   x   9                      
   無法得到B列值  ---------------------------------------------------------------------------------------------

RANK() OVER (PARTITION BY COL1 ORDER BY COL2)
類似，不過RANK 排序的時候跟派名次一樣，可以並列2個第一名之後 是第3名。

dense_rank() OVER (PARTITION BY COL1 ORDER BY COL2)
類似。是連續排序，有兩個第二名時仍然跟著第三名。

dept sal emp row_number rank dense_rank
---- ------- ----- ---------- ----- ----------
10 5000.00 7839 1 1 1
10 2450.00 7782 2 2 2
10 1300.00 7934 3 3 3
20 3000.00 7788 1 1 1
20 3000.00 7902 2 1 1
20 2975.00 7566 3 3 2
20 1100.00 7876 4 4 3
20 800.00 7369 5 5 4
30 2850.00 7698 1 1 1
30 1600.00 7499 2 2 2

 

在求第一名成績的時候，不能用row_number()，因為如果同班有兩個並列第一，row_number()只返回一個結果

oracle row_number()