sqlserver查询排名问题

[coder_lee_1984]

假设我有一个表：
表中记录了用户的积分，假设表结构如下：
userid money date
三个字段分别代表用户id，用户积分，以及日期
我现在想实现一个排名统计，要根据用户id查询某个具体用户在所有用户中的排名，查询该用户的积分(money)，
同时还要查询出该用户距离他上一名用户的积分差距。比如：某用户当前积分10000，通过查询获得该用户排名为第三，
那么同时也要查询到第二名的积分，因为要统计第三名距离第二名差多少积分。
不知道这样的需求在sqlserver里该怎么写

[spiritofdragon]

先确定下，你这表是获得积分的记录表，还是当前累计积分表？如果是前者，先汇总后再排名。如果是后者如下

with t(userid,money ,date) as (
select 1,100,DATEADD(DD,-5,GETDATE())
union all
select 2,120,DATEADD(DD,-4,GETDATE())
union all
select 3,12,DATEADD(DD,-3,GETDATE())
union all
select 4,31,DATEADD(DD,-2,GETDATE())
union all
select 5,111,DATEADD(DD,-1,GETDATE())
)
,t1 as (
select 
	*,ROW_NUMBER()over(order by money desc) rnk
from t
)
select t1.* 
	,ISNULL(t2.money-t1.money,0) diff
from t1 left join t1 t2 on t1.rnk=t2.rnk+1

[道玄希言]

;WITH cte(userid,[money],[date]) AS 
(
    select 1, 700, '2015-08-01' union all
    select 2, 830, '2015-08-06' union all
    select 3, 540, '2015-08-02' union all
    select 4, 160, '2015-09-01' union all
    select 5, 400, '2015-09-11' union all
    select 6, 200, '2015-08-05'
),
cte2 as
(
select 
  ROW_NUMBER() OVER(order by [money] desc) as nid,
* from cte
)
select nid, [money],[date],
  ((select [money] from cte2 as a 
  where a.nid = (t.nid-1))-[money]) as diffmoney
from cte2 as t 
where userid = 4 

[Tiger_Zhao]

要考虑并列排名。

WITH
/* 测试数据
table1(userid,money)AS(
    SELECT 1,250 UNION ALL
    SELECT 2,200 UNION ALL
    SELECT 3,200 UNION ALL
    SELECT 4,180
), */
a AS (
    SELECT userID,
           money,
           RANK() OVER(ORDER BY money DESC) AS r1,
           DENSE_RANK() OVER(ORDER BY money DESC) AS r2
      FROM table1
),
b AS (
    SELECT DISTINCT
           money,
           r2
      FROM A
)
    SELECT a.userID,
           a.money,
           a.r1 AS 排名,
           b.money - a.money AS 差距
      FROM a
 LEFT JOIN b
        ON a.r2 = b.r2 + 1
  -- WHERE a.userID = @userID

     userID       money                 排名        差距
----------- ----------- -------------------- -----------
          1         250                    1        NULL
          3         200                    2          50
          2         200                    2          50
          4         180                    4          20