Sql Server2005对t-sql的增强之排名函数
if object_id('student_class_grade','U') is not null drop table student_class_grade;GO
create table student_class_grade
(
student_id int, --学生id
class_no int, --班级编号
grade int --成绩
);
GO
INSERT INTO student_class_grade select 1,1,90
union all select 2,1,85
union all select 3,1,80
union all select 4,1,80
union all select 5,1,90
union all select 6,1,75
union all select 7,1,89
union all select 11,2,90
union all select 12,2,85
union all select 13,2,80
union all select 14,2,80
union all select 15,2,90
union all select 16,2,75
union all select 17,2,89
GO
--显示各个班级学生的成绩排名
SELECT student_id
,class_no,grade
,'名次' = RANK() OVER(PARTITION BY> FROM student_class_grade
GO
SELECT student_id
,class_no,grade
,'名次' = DENSE_RANK() OVER(PARTITION BY> FROM student_class_grade
页:
[1]