查询子查询

Question

我必须显示我计划在SQL Server中使用Rank()函数的用户的Rank。获取排名的Sql查询如下所示

select a.user_id, RANK() OVER (ORDER BY (a.quant_points) DESC) AS QRANK,
    RANK() OVER (ORDER BY (a.verbal_points) DESC) AS VRANK,
    RANK() OVER (ORDER BY (a.dilr_points) DESC) AS DRANK,
    RANK() OVER (ORDER BY (a.quant_points+a.verbal_points+a.dilr_points) DESC) AS ORANK
 from users a

但是，我只想获得特定用户的排名。如果我在上面添加一个where子句(where user_id = @user_id)，那么所有的等级都显示为1。

因此，本质上，我必须将上面的结果集作为一个表进行查询。我正在考虑创建一个临时表，但我觉得可能有更好的方法来实现这一点。

Answer 1

创建一个CTE，并把你的单个用户拉出来怎么样？

;with  UserRank AS
( 
    SELECT a.user_id, RANK() OVER (ORDER BY (a.quant_points) DESC) AS QRANK,
           RANK() OVER (ORDER BY (a.verbal_points) DESC) AS VRANK,
           RANK() OVER (ORDER BY (a.dilr_points) DESC) AS DRANK,
           RANK() OVER (ORDER BY (a.quant_points+a.verbal_points+a.dilr_points) DESC) AS ORANK
   FROM Users a
 )

 SELECT QRANK, VRANK, DRANK, ORANK, user_id
 FROM UserRank WHERE user_id = @foo