SQL Server four kinds of sorts: Row_number ()/rank ()/dense_rank ()/ntile () over ()

Today women vote asked me SQL Server four sort, on the spot wrote a few words SQL let her understand, now put related SQL put up.

First, we create some test data.

if object_id('tempdb.dbo. #Tmp') is  not NULL    Drop Table#TmpCreate Table#Tmp (Namenvarchar(Ten))Insert  into#TmpSelectN'Zhang San'UnionSelectN'John Doe'Union SelectN'Harry'Union SelectN'Zhao Liu'UnionSelectN'Zhu Qi'UnionSelectN'Bastard'Union  AllSelectN'Zhang San'

The last Union is the union all, because we have more than one line of "Zhang San".

One, Row_number () over (partition by ColumnName ORDER by ColumnName)

Select Row_number ()over (order by as num,* from #Tmp

You can get a result set sorted by name.

Row_number () over () also has a usage that can be grouped for a column.

The following results show that Zhang San has 1 and 22 sorts, while the other names are sorted by only 1.

Select Row_number ()throughorder by as num,* from #Tmp

RANK () over (order by ColumnName)

You can see from the result set below that the result set is numbered 5 less, and has two 4 numbers and jumps directly to number 6.

Select RANK ()over [orderby name],* from #Tmp

Third, Dense_rank () over (order by ColumnName)

Select Dense_rank ()over (orderby name),* from #Tmp

After executing the SQL, it is found that the following result set has 2 numbered 4 lines followed by the number 5 lines.

The Dense_rank () function is similar to the RANK () function.

The RANK () function is divided into groups, and the final number must be the same as the number of rows.

The last number of the Dense_rank () function is related to the number of groupings.

Iv. NTILE () over (ORDER by COLUMNNAME)

Select NTILE (2)over (orderby name),* from  #TmpSelect NTILE (3)over (orderby name),* from #Tmp

The number behind the ntile is to divide the results of the query evenly into groups.
As divided into 2 and 3 groups.
If the number of rows is divided evenly and there are Yu Xing, then the rows are divided into the first groups.
For example, we have 7 rows of results to be divided into 3 groups.
Then the first group of 3 rows, the second group 2 rows, the third group 2 rows.
If we result in 14 rows, the average is divided into 3 groups.
Then the first group of 5 rows, the second group 5 rows, the third group 4 rows.
And so on

SQL Server Four sorts: Row_number ()/rank ()/dense_rank ()/ntile () over ()