mysql - 在选择排名中选择()

Question

有人能告诉我如何从结果查询中选择特定的用户名及其排名吗？我正在尝试检索 FoxDown 的排名。

我相信我需要以某种方式从此查询中进行选择选择用户名，my_rank(选择....。

当前查询

    SELECT
    username,
    SUM(goals) as Diff,
    RANK() OVER (ORDER BY Diff DESC) my_rank
    FROM results
    WHERE Week = 'Week 23'
    GROUP by username
    ORDER by my_rank

查询结果表

username  Diff  my_rank
Sugar      12     1
Coj        10     2
Gunners     8     3
Captain     6     4
Spoon       6     4
Ric         6     4
FoxDown     6     4
shau        4     8
Josh        4     8
nol         4     8
pesty       4     8
Hill        4     8
Jg          4     8

Answer 1

Window functions are calculated after the where clause, logically .因此，您必须将窗口函数计算嵌套在派生表中，然后对其进行过滤:

SELECT *
FROM (
  SELECT
    username,
    SUM(goals) as Diff,
    RANK() OVER (ORDER BY SUM(goals) DESC) my_rank
  FROM results
  WHERE Week = 'Week 23'
  GROUP by username
) t
WHERE username = 'FoxDown'

请注意，您不能从同一SELECT 级别引用列别名，因此您需要在排名计算中重复SUM(goals) 聚合函数。这再次起作用，因为从逻辑上讲，聚合函数是在 窗口函数之前计算的(请参阅相同的链接博客文章)