mysql - 从添加增量列的位置选择

Question

我有一个查询，它创建一个结果表/阶梯并将它们排序在最终位置。我现在尝试进一步分解它，并从该查询中获取特定的用户名及其位置。

我认为只需将此查询添加到普通选择中就可以了，但这似乎不起作用，有什么想法吗？

这将创建包含所有用户名及其位置的完整结果表，

SELECT @r := @r+1 as Post , z.* FROM (
SELECT username, memid, SUM(CASE WHEN goals >= 0 THEN goals ELSE 0 END) AS Pos, SUM(CASE WHEN goals < 0 THEN -1*goals ELSE 0 END) AS Neg, SUM(goals) AS Diff 
FROM presults WHERE Week = 'Week 2' 
GROUP BY username 
ORDER BY Diff DESC, Pos DESC, memid asc )z, 
(SELECT @r:=0)y

我现在想选出一名成员，

即:

SELECT username, Position FROM (
SELECT @r := @r+1 as Position , z.* FROM (
SELECT username, memid, SUM(CASE WHEN goals >= 0 THEN goals ELSE 0 END) AS Pos, SUM(CASE WHEN goals < 0 THEN -1*goals ELSE 0 END) AS Neg, SUM(goals) AS Diff 
FROM presults WHERE Week = 'Week 2' 
GROUP BY username 
ORDER BY Diff DESC, Pos DESC, memid asc )z, 
(SELECT @r:=0)y
) WHERE username = "John"

Answer 1

SELECT username, Position FROM (
SELECT @r := @r+1 as Position , z.* FROM (
SELECT username, memid, SUM(CASE WHEN goals >= 0 THEN goals ELSE 0 END) AS Pos, SUM(CASE WHEN goals < 0 THEN -1*goals ELSE 0 END) AS Neg, SUM(goals) AS Diff 
FROM presults WHERE Week = 'Week 2' 
GROUP BY username 
ORDER BY Diff DESC, Pos DESC, memid asc )z, 
(SELECT @r:=0)y
)T1 WHERE username = "John"