作者：辛亚平
日期：2007年11月13日
［问题］
以SQL Server 2005为例。有下列三个表：
学生信息表StudInfo、考试成绩表ExamResult、成绩排行表ScoreList
其创建语句如下：
create table StudInfo (
StudID int primary key
, StudName nvarchar(10)
, StudSex bit
)
create table ExamResult (
ExamID int
, StudID int
, Score int
)
create table ScoreList (
RecID int primary key identity( 1, 1 )
, ExamID int
, ListID int
, StudID int
, StudName nvarchar(10)
, Score int
)
字段说明：ListID为每次考试的成绩排名。
测试用的数据如下：
insert into StudInfo values( 7091, N'张三', 1 )
insert into StudInfo values( 7092, N'李四', 1 )
insert into StudInfo values( 7093, N'李萍', 0 )
insert into StudInfo values( 7094, N'王五', 1 )
insert into StudInfo values( 7095, N'李平', 1 )
insert into StudInfo values( 7096, N'赵丽', 0 )
insert into ExamResult values( 8041, 7091, 87 )
insert into ExamResult values( 8041, 7092, 76 )
insert into ExamResult values( 8041, 7093, 94 )
insert into ExamResult values( 8041, 7094, 67 )
insert into ExamResult values( 8041, 7095, 96 )
insert into ExamResult values( 8041, 7096, 89 )
insert into ExamResult values( 8042, 7091, 98 )
insert into ExamResult values( 8042, 7092, 73 )
insert into ExamResult values( 8042, 7093, 81 )
insert into ExamResult values( 8042, 7094, 76 )
insert into ExamResult values( 8042, 7095, 95 )
insert into ExamResult values( 8042, 7096, 97 )
现在要将 ExamID = 8042 的这次考试成绩排行榜插入到成绩排行表ScoreList中。
--------------------------------------------------------------------------------
［方法］
成绩排行表ScoreList中所有的数据都可以直接从学生信息表StudInfo、考试成绩表ExamResult中查询得到，唯独名次字段ListID无法直接得到。在Oracle数据库中有@@RowID，可以让我们轻松得到查询记录的行号，从而得到ListID的值，但是在SQL Server中不支持@@RowID（以SQL Server 2005为例。不排除SQL Server以后的版本支持它的可能）。
ListID字段的值我们可以通过以下三种方法赋予：
1、用外部程序实现：用数据库应用程序，将记录从学生信息表StudInfo、考试成绩表ExamResult中一条一条读出来，再写回成绩排行表ScoreList中，写回的时候赋ListID字段的值。
2、在SQL Server Management Studio中运行一段SQL语句，通过游标赋ListID字段的值。
declare @StudID int
declare @StudName nvarchar(10)
declare @Score int
declare @ExamID int
declare @ListID int
set @ExamID = 8042
set @ListID = 1
declare pScoreList cursor for
select ExamResult.StudID, StudInfo.StudName, ExamResult.Score from ExamResult inner join StudInfo on ExamResult.StudID = StudInfo.StudID where ExamID = 8042 order by Score desc
open pScoreList
fetch next from pScoreList into @StudID, @StudName, @Score
insert into ScoreList values( @ExamID, @ListID, @StudID, @StudName, @Score )
While @@FETCH_STATUS = 0
Begin
set @ListID = @ListID + 1
fetch next from pScoreList into @StudID, @StudName, @Score
insert into ScoreList values( @ExamID, @ListID, @StudID, @StudName, @Score )
End
close pScoreList
DeAllocate pScoreList
3、通过临时表实现。我们可以在临时表中建立一个自增的主键，先将记录按顺序写到临时表中去，这时临时表的主键值就是这次考试的排名。也可以不通过自增主键来实现，通过在select into语句中加入 identity( int, 1, 1 ) 字段来实现：
select ExamResult.ExamID, identity( int, 1, 1 ) as ListID, ExamResult.StudID, StudInfo.StudName, ExamResult.Score into #ScoreList_8042 from ExamResult inner join StudInfo on ExamResult.StudID = StudInfo.StudID where ExamID = 8042 order by Score desc
insert into ScoreList select * from #ScoreList_8042
drop table #ScoreList_8042
4、通过函数 Row_Number() ，再配合关键字 Over，来实现排名。这样可以仅用一条SQL语句实现要求。
--------------------------------------------------------------------------------
［用 Row_Number() 函数通过一条SQL语句实现］
insert into ScoreList select ExamResult.ExamID, Row_Number() over ( order by Score desc ) AS ListID, ExamResult.StudID, StudInfo.StudName, ExamResult.Score from ExamResult inner join StudInfo on ExamResult.StudID = StudInfo.StudID where ExamID = 8042
--------------------------------------------------------------------------------
［检验］
select * from ScoreList
==============================================
RecID ExamID ListID StudID StudName Score
----------------------------------------------
1 8042 1 7091 张三 98
2 8042 2 7096 赵丽 97
3 8042 3 7095 李平 95
4 8042 4 7093 李萍 81
5 8042 5 7094 王五 76
6 8042 6 7092 李四 73
==============================================
' 
'看， 
