SQL Server ranking function implementation

There are four ranking functions in SQL Server, namely:

1, Row_number ()

2, Ntile ()

3. Rank ()

4, Dense_rank ()

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

To facilitate demonstration, we first set up related tables

CREATE TABLE T (ID int, Name varchar (32));
Go

Insert into T (id,name) VALUES (1, ' a '), (2, ' a '), (3, ' B '), (4, ' C '), (5, ' C '), (6, ' D ');
Go

1.

Row_number ();

Select Id,name,row_number () over (order by Name) as RowNumber

From T;

Go

　　　　

Select Id,name, (select COUNT (*) from T as B where b.id<=a.id) as RowNumber
From T as A;
Go

　　　　

2.

Ntile ()

Select Id,name,ntile (3) over (order by ID) Nitlenumber from T;

Go

　　　　

　　　　DECLARE @count as int = 6;

DECLARE @groupsize as int = 3;
declare @tilesize as int [email protected]/@groupsize;

Select Id,name, ((SELECT COUNT (*) +1 from T as b where a.id>=b.id)/@tilesize) Ntilenumber
From T as A;
Go

　　　　

3.

Rank ()

Select Id,name,rank () over (order by Name)
From T;
Go

　　　　

　　　　Select Id,name, (select COUNT (*) +1 from T as B where b.name <a.name)

　　　　From T as A;

Go

　　　　

　　　　

SQL Server ranking function implementation