After SQLServer2005, The row_number () function is introduced. The grouping and sorting function of row_number () makes this operation very simple.
After SQL Server 2005, The row_number () function is introduced. The grouping and sorting function of row_number () makes this operation very simple.
Grouping TOP data is a common query in the T-SQL, such as the student information management system to take out the first three students of each subject. Before SQL Server 2005, such queries are cumbersome to write and can be obtained only when temporary table join queries are used. After SQL Server 2005, The row_number () function is introduced. The grouping and sorting function of row_number () makes this operation very simple. The following is a simple example:
The Code is as follows:
-- 1. Create a test table
Create table # score
(
Name varchar (20 ),
Subject varchar (20 ),
Score int
)
-- 2. Insert Test Data
Insert into # score (name, subject, score) values ('zhang san', 'China', 98)
Insert into # score (name, subject, score) values ('zhang san', 'mat', 80)
Insert into # score (name, subject, score) values ('zhang san', 'English ', 90)
Insert into # score (name, subject, score) values ('Li si', 'China', 88)
Insert into # score (name, subject, score) values ('Li si', 'mat', 86)
Insert into # score (name, subject, score) values ('Li si', 'English ', 88)
Insert into # score (name, subject, score) values ('lilim', 'China', 60)
Insert into # score (name, subject, score) values ('lilim', 'mat', 86)
Insert into # score (name, subject, score) values ('Li Ming ', 'English', 88)
Insert into # score (name, subject, score) values ('lin feng', 'China', 74)
Insert into # score (name, subject, score) values ('lin feng', 'mat', 99)
Insert into # score (name, subject, score) values ('lin feng', 'English ', 59)
Insert into # score (name, subject, score) values ('yan ming', 'English ', 96)
-- 3. Retrieve the first three data entries of 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 a temporary table
Truncate table # score
Drop table # score
Syntax format: ROW_NUMBER () OVER (partition by COL1 order by COL2)
Explanation: group by COL1, sort by COL2 within the group, and the value calculated by this function indicates the sequential number after sorting in each group (continuous and unique in the Group)
