sqlserver- 排名练习

-- Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.
USE master
GO

IF EXISTS(SELECT * FROM sysdatabases WHERE name='LeetCode')
	BEGIN
		DROP DATABASE LeetCode
	END
GO
CREATE DATABASE LeetCode
GO
USE LeetCode
GO

IF EXISTS(SELECT * FROM sys.objects WHERE name='Scores')
	DROP TABLE Scores
GO
CREATE TABLE Scores
(
	Id INT IDENTITY PRIMARY KEY,
	Score float
)
INSERT INTO Scores VALUES
	(3.50), (3.65), (4.00), (3.85), (4.00), (3.65)
GO

SELECT *
FROM Scores
GO

SELECT *, ROW_NUMBER() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores

SELECT *, RANK() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores

SELECT *, DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores

SELECT *, NTILE(5) OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores