Rank () and dense_rank () are used for classification ranking.

Oracle classification sorting/ranking can be achieved through the Aggregate functions rank () and dense_rank.
Rank () and dense_rank ():
-- Two ranking methods (partition and no partition): use and do not use Partition
-- Two calculation methods (continuous and discontinuous), corresponding function: dense_rank, rank
The usage of dense_rank is the same as that of rank (), but there is a difference: When dence_rank is in a parallel relationship, the correlation level is not skipped, that is, continuous. Rank is skipped, that is, not consecutive.

 

Code
Select SQ,
Dwmc,
Decode (mbzql *   100   |   ' % ' , ' % ' , '' , Mbzql *   100   |   ' % ' ),
Decode (fhycnpjzql *   100   |   ' % ' , ' % ' , '' , Fhycnpjzql *   100   |   ' % ' ),
Decode (zdfhycnzhzql *   100   |   ' % ' , ' % ' , '' , Zdfhycnzhzql *   100   |   ' % ' ),
Decode (zxfhycnzhzql *   100   |   ' % ' , ' % ' , '' , Zxfhycnzhzql *   100   |   ' % ' ),
Decode (dlycnzql *   100   |   ' % ' , ' % ' , '' , Dlycnzql *   100   |   ' % ' ),
Decode (nfhyczhzql *   100   |   ' % ' , ' 0% ' , '' , Nfhyczhzql *   100   |   ' % ' ),
Rank () Over (Partition By SQ Order   By Nfhyczhzql Desc ) New_rank,
Case To_char ( Floor (Nfhyczhzql *   100   - Mbzql *   100 ))
When   ' 0 '   Then
' First Class '
When   ' -1 '   Then
' Excellent '
When   ' -2 '   Then
' Compliance '
Else
'   '
End "Khpj"
From ( Select T. ssdq SQ,
T. dwmc,
T. mbzql,
T. fhycnpjzql,
T. zdfhycnzhzql,
T. zxfhycnzhzql,
T. dlycnzql,
Nvl (T. nfhyczhzql, 0 ) Nfhyczhzql
From Mw_app.pm_nw_fs_pz_ndpmzb t
Where To_char (jssj, ' Yyyy ' ) =   ' 2008 '
And Dwmc Is   Not   Null
Order   By SQ, nfhyczhzql)

 

Differences between Oracle and integer Retrieval
1. INTEGER (large)
Select Ceil (-1.001) value from dual
2. Round (small)
Select floor (-1.001) value from dual
3. Round (truncation)
Select trunc (-1.002) value from dual
4. Round (rounding)
Select round (-1.001) value from dual

It should be noted that if the field value is dbnull, the system will automatically sort it as 1. Here we will first handle the default value.