SQL - ROW_NUMBER,Rank 添加序号列

百度的时候查到的博客:

http://blog.csdn.net/xsfqh/article/details/6663895
-----------------------------------------------------------------------------------------------------------------------

其中内容:

给查询出的SQL记录添加序号列,解决方法有以下两种

第一:

select ROW_NUMBER() OVER (ORDER BY a.字段 ASC) AS XUHAO,a.* from table a

(table 为表名,字段为表a中的字段名)

第二:

select RANK()  OVER (ORDER BY a.字段 ASC) AS XUHAO,a.* from table a

(table 为表名,字段为表a中的字段名)

-----------------------------------------------------------------------------------------------------------------------

在需要获取最高分数从而获取排序的写法:

select ROW_NUMBER() OVER (ORDER BY Max(BearPickGold012Play.Score) DESC) AS xuhao,Max(BearPickGold012Play.Score) as Score,WeixinUserID from  BearPickGold012Play where ActivitySceneID=1  GROUP BY WeixinUserID
select Rank() OVER (ORDER BY Max(BearPickGold012Play.Score) DESC) AS xuhao,Max(BearPickGold012Play.Score) as Score,WeixinUserID from BearPickGold012Play where ActivitySceneID=1 GROUP BY WeixinUserID
 

总体来说,ROW_NUMBER(或者RANK)方法就是将需要排序的对象圈入,然后其他的不变,按照自己本身的筛选方式进行操作即可

上一篇:AS3 转 Java


下一篇:IC系统组成概论