在网上看了不少关于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()!