Reprint Address: http://www.cnblogs.com/linJie1930906722/p/6036053.html
The partition by keyword is part of the analytic function, which differs from the aggregation function in that it can return multiple records in a group, whereas aggregate functions generally have only one record that reflects the statistical value, and partition by is used to group the result set. If not specified then it takes the entire result set as a grouping, and the partition function is generally used with the rank function.
To prepare the test data:
CREATE TABLE Student --Student score table (ID int, --primary key Grade int,--Class score int--score) Goinsert into Student values (1,1,88) inse RT into Student values (2,1,66) insert into Student values (3,1,75) insert into Student values (4,2,30) insert INTO Student valu ES (5,2,70) insert into Student values (6,2,80) insert to Student values (7,2,60) insert into Student values (8,3,90) insert into Student values (9,3,70) insert to Student values (10,3,80) insert into Student values (11,3,80)
The usage of partition function partition by and row_number ()
1, do not class according to student performance rankings
Select *,row_number () over (order BY score Desc) as Sequence from Student
Execution Result:
2. Ranked by student score after class
Select *,row_number () over (partition by Grade ORDER BY score Desc) as Sequence from Student
Execution Result:
3. Get the first 1 (few) names of each class
SELECT * FROM (select *,row_number () over (partition by Grade ORDER BY score Desc) as Sequence from Student) T where T.sequen Ce<=1
Execution Result:
The use of the partition function partition by and ranking rank ()
1, after the class according to the student performance ranking this statement is the same score of the record of the same rank, for example: two 80 points in the 2nd place, 4th place No
Select *,rank () over (partition by Grade ORDER BY score Desc) as Sequence from Student
Execution Result:
2. Get the first 2 (several) of each class The statement is the same as the score of the same record, for example: two 80 points in the 2nd place, 4th place No
in simple terms, it is similar to row_number, except that it processes the order by field, and if the field value is the same, the line number remains unchanged.
SELECT * FROM (select *,rank () over (partition by Grade ORDER BY score Desc) as Sequence from Student) T where t.sequence< =2
Execution Result:
The use of the
partition function partition by and the usage of row_number () and the use of ranking rank () are explained in detail (get the first few records in the Grouping (partition)) (GO)