SQLServer2005中用SQL语句生成序号列

在网上看了不少关于SQL语句生成排序列的例子，有写临时表的，有做Sum计算的！

还有些根本一知半解的，跑来回答问题，弄出的语句无法运行的，不懂装懂害死人啊！

 

以下语句针对的情况是：我要根据一个表进行统计计算，然后根据统计的结果HCheckBefore字段排名！

生成一个列（表中并不存在），显示排名！语句经过10几次的模拟测试，准确无误！希望给大家有所帮助！

 

Select A.WorkStation,A.Team,SiteNo=Count(*),
 HCheckBefore=IsNull((Select Sum(FailDispScaleTime) From CM_Maint_Site_Failure Where Rtrim(Ltrim(CheckDeptName))<>'四方公司' And A.Team=Team),0),
 HCheckAfter=IsNull((Select Sum(FailDispScaleTime) From CM_Maint_Site_Failure Where Rtrim(Ltrim(CheckDeptName))='四方公司' And A.Team=Team),0),
 MCheckBefore=IsNull((Select Sum(FailDispScaleTime)*60 From CM_Maint_Site_Failure Where Rtrim(Ltrim(CheckDeptName))<>'四方公司' And A.Team=Team),0)/Count(*),
 MCheckAfter=IsNull((Select Sum(FailDispScaleTime)*60 From CM_Maint_Site_Failure Where Rtrim(Ltrim(CheckDeptName))='四方公司' And A.Team=Team),0)/Count(*),
 row_number() over (order by (Select Sum(FailDispScaleTime) From CM_Maint_Site_Failure Where Rtrim(Ltrim(CheckDeptName))<>'四方公司' And A.Team=Team)) as rowid
 From CM_Maint_Site_Failure A
Group by A.WorkStation,A.Team

 

还好SQL2005有函数row_number()!