ROW_NUMBER(),RANK(),DENSE_RANK()区别

 一、ROW_NUMBER() 

SELECT      ITEM_CODE,	
            PO_NO,	
            PO_LINE_NO,	
            PO_AMT,
            ROW_NUMBER() OVER(PARTITION BY PO_NO ORDER BY ITEM_CODE) AS NUM 	
FROM MRO.STX_MRO_PO_LINE 	
WHERE PO_NO='RP111110-0016';

     

    执行结果：

 

ITEM_CODE	PO_NO	PO_LINE_NO	PO_AMT	NUM
M90202-0023	RP111110-0016	9	188000	1
M90202-0023	RP111110-0016	5	235000	2
M90202-0028	RP111110-0016	10	33440	3
M90202-0057	RP111110-0016	6	6380	4
M90202-0167	RP111110-0016	8	325600	5
M90202-0167	RP111110-0016	7	130240	6
M90205-0011	RP111110-0016	1	1920	7
M90205-0015	RP111110-0016	2	9120	8
M90205-0016	RP111110-0016	3	16500	9
M90205-0017	RP111110-0016	4	2986	10

 

二、RANK() 

SELECT      ITEM_CODE,	
            PO_NO,	
            PO_LINE_NO,	
            PO_AMT,
            RANK() OVER(PARTITION BY PO_NO ORDER BY ITEM_CODE) AS NUM 	
FROM MRO.STX_MRO_PO_LINE 	
WHERE PO_NO='RP111110-0016';

  

执行结果：

 

ITEM_CODE	PO_NO	PO_LINE_NO	PO_AMT	NUM
M90202-0023	RP111110-0016	9	188000	1
M90202-0023	RP111110-0016	5	235000	1
M90202-0028	RP111110-0016	10	33440	3
M90202-0057	RP111110-0016	6	6380	4
M90202-0167	RP111110-0016	8	325600	5
M90202-0167	RP111110-0016	7	130240	5
M90205-0011	RP111110-0016	1	1920	7
M90205-0015	RP111110-0016	2	9120	8
M90205-0016	RP111110-0016	3	16500	9
M90205-0017	RP111110-0016	4	2986	10

 

 

三、DENSE_RANK() 

SELECT      ITEM_CODE,	
            PO_NO,	
            PO_LINE_NO,	
            PO_AMT,
            DENSE_RANK() OVER(PARTITION BY PO_NO ORDER BY ITEM_CODE) AS NUM 	
FROM MRO.STX_MRO_PO_LINE 	
WHERE PO_NO='RP111110-0016';

  

执行结果：

 

ITEM_CODE	PO_NO	PO_LINE_NO	PO_AMT	NUM
M90202-0023	RP111110-0016	9	188000	1
M90202-0023	RP111110-0016	5	235000	1
M90202-0028	RP111110-0016	10	33440	2
M90202-0057	RP111110-0016	6	6380	3
M90202-0167	RP111110-0016	8	325600	4
M90202-0167	RP111110-0016	7	130240	4
M90205-0011	RP111110-0016	1	1920	5
M90205-0015	RP111110-0016	2	9120	6
M90205-0016	RP111110-0016	3	16500	7
M90205-0017	RP111110-0016	4	2986	8