查询子查询

Question

我必须显示计划在SQL Server中使用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子句（其中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