Grouping top data is a common query in T-SQL, such as a student information management system that takes 3 students out of each subject. This query is cumbersome to write before SQL Server 2005 and requires a temporary table associated query to be fetched. After SQL Server 2005, the Row_number () function is introduced, and the sorting function of the row_number () function makes this operation very simple. The following is a simple example:
Copy Code code as follows:
--1. Create a test table
CREATE TABLE #score
(
Name varchar (20),
Subject varchar (20),
Score int
)
--2. Inserting test data
Insert into #score (Name,subject,score) VALUES (' John ', ' language ', 98)
Insert into #score (Name,subject,score) VALUES (' John ', ' math ', 80)
Insert into #score (Name,subject,score) VALUES (' John ', ' English ', 90)
Insert into #score (Name,subject,score) VALUES (' Dick ', ' language ', 88)
Insert into #score (Name,subject,score) VALUES (' Dick ', ' math ', 86)
Insert into #score (Name,subject,score) VALUES (' Dick ', ' English ', 88)
Insert into #score (Name,subject,score) VALUES (' Li Ming ', ' language ', 60)
Insert into #score (Name,subject,score) VALUES (' Li Ming ', ' math ', 86)
Insert into #score (Name,subject,score) VALUES (' Li Ming ', ' English ', 88)
Insert into #score (Name,subject,score) VALUES (' Lin Feng ', ' language ', 74)
Insert into #score (Name,subject,score) VALUES (' Lin Feng ', ' math ', 99)
Insert into #score (Name,subject,score) VALUES (' Lin Feng ', ' English ', 59)
Insert into #score (Name,subject,score) VALUES (' strict ', ' English ', 96)
--3. Take the top 3 data from each subject
SELECT * FROM
(
Select Subject,name,score,row_number () over (PARTITION by subject ORDER BY score desc) as num from #score
T where T.num <= 3 order by subject
--4. Delete temporary tables
TRUNCATE TABLE #score
drop table #score
Syntax form: row_number () Over (PARTITION by COL1 ORDER by COL2)
Explanation: According to the COL1 group, sorted within the group according to the COL2, and the value of this function represents the sequential number after each group's internal sort (consecutive unique in the group)
