Sql Server2005 new addition to the four ranking functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE; we must have on the ROW_NUMBER very familiar with, so I start from the last NTILE analysis.
NTILE in msdn in the explanation: the orderly distribution of the partition line to the specified number of groups. Each group number, numbered from the start. For each row, NTILE returns this Bank's group number. I do not know can not understand this explanation at once, anyway I wrote a combination of examples to explain their only find out quick.
Prepared script, we created a simple 3-list, three are the id, categoryId, and the name, as follows:
GOif object_id (''t_ntile'','' U'') is not nulldrop table t_ntile; GOcreate table t_ntile (id int unique not null, categoryId int not null, name nvarchar (20)) goINSERT INTO t_ntile VALUES (1,1 ,''A'') INSERT INTO t_ntile VALUES (2,4,''B'') INSERT INTO t_ntile VALUES (3,2,''C'') INSERT INTO t_ntile VALUES (4,1,''D ' ') INSERT INTO t_ntile VALUES (5,3,''E'') INSERT INTO t_ntile VALUES (6,3,''F'') INSERT INTO t_ntile VALUES (7,2,''G'') INSERT INTO t_ntile VALUES (8,2,''H'') INSERT INTO t_ntile VALUES (9,2,''I'') Go
Query as follows:
SELECT id, categoryId, name,''ntile value''= NTILE (3) OVER (PARTITION BY categoryId ORDER BY categoryId) FROM t_ntile
We pass the parameters to NTITL 3, means that each district will be divided into three groups, and then specify the OVER in the expression of categoryId to split according to group and sort in accordance with categoryId. The implementation of the above expression as follows:
Figure 1
(To view artwork, please click: Figure 1 Original)
Figure 1 Note: derived according to the conditions given in question: the result set, all the lines will be in accordance with the categoryId divided into areas, each district is divided into 3 groups (see above).
categoryId 1 of two lines: id = 1 line was assigned to group 1, id = 4 line was assigned to Group 2, 1st District at this time has been allocated.
categoryId 2 of four lines: id = 7, id = 8 lines are assigned to group 1, id = 9 line is assigned to group 2, id = 3 line was assigned to group 3, the second partition has been allocated.
categoryId 3 two lines: id = 5 line was assigned to group 1, id = 6 line was assigned to group 2, the third district allocated.
categoryId 4 line: id = 2 lines were assigned to Group 1, the fourth partition allocated.
Thus the result set of all rows have been assigned. Members of each group as shown above.
See below and DENSE_RANK the brothers RANK function, this function is easy to understand than NTITL number. MSDN explanation on RANK: return result sets row position of each sub-region. Line before the ranking is the ranking of the relevant line number plus one. DENSE_RANK explanation on MSDN are: Bank of China to return the result set ranking district in the rankings without any interruption. Row rank equal to the prior discussion of all the top line number plus one. Now I will explain with an example, explain their differences with the results:
if object_id (''student_class_grade'','' U'') is not nulldrop table student_class_grade; GOcreate table student_class_grade (student_id int, - students idclass_no int, - the class number grade int - result); GOINSERT INTO student_class_grade VALUES ( 1,1,90); INSERT INTO student_class_grade VALUES (2,1,85); INSERT INTO student_class_grade VALUES (3,1,80); INSERT INTO student_class_grade VALUES (4,1,80); INSERT INTO student_class_grade VALUES (5, 1,90); INSERT INTO student_class_grade VALUES (6,1,75); INSERT INTO student_class_grade VALUES (7,1,89); INSERT INTO student_class_grade VALUES (11,2,90); INSERT INTO student_class_grade VALUES (12,2, 85); INSERT INTO student_class_grade VALUES (13,2,80); INSERT INTO student_class_grade VALUES (14,2,80); INSERT INTO student_class_grade VALUES (15,2,90); INSERT INTO student_class_grade VALUES (16,2,75) ; INSERT INTO student_class_grade VALUES (17,2,89); GO - Display the results of students in all class rankings SELECT student_id, class_no, grade,''Rank''= RANK () OVER (PARTITION BY class_no ORDER BY grade desc) FROM student_class_gradeGOSELECT student_id, class_no, grade,''Rank''= DENSE_RANK () OVER (PARTITION BY class_no ORDER BY grade desc) FROM student_class_grade
To execute the following two select script, you can obtain the following results
Figure 1
Can see that a class ranking of students have tied for first followed by 1,1,3,4 after the second sort is 3.
The implementation of the following is DENSE_RANK results:
Figure 1
Can see that rank order is 1,1,2,3 ... When, after the two tied for first, second ranking is 2, rather than the RANK of 3. So we can be ranked to student achievement, rather than using DENSE_RANK RANK.
Finally we introduce the function ROW_NUMBER This page provided to our convenience. We can combine the CTE (Common Table Expressions) used as an example
WITH CTE_rn (student_id, class_no, grade, rn) AS (SELECT student_id, class_no, grade, rn = ROW_NUMBER () OVER (ORDER BY student_id ASC) FROM student_class_gradeWHERE 0 = 0 - you can add some filters in here, so the following The paging sql do not need additional conditions on the) - were the first clause of the data -10 SELECT student_id, class_no, grade FROM CTE_rn WHERE rn BETWEEN 6 AND 10; SELECT totalCn = COUNT (*) FROM student_class_grade WHERE 0 = 0