我有此查询，它将创建一个结果表/梯形图并将其排列在最终位置。我现在正试图将其进一步细分，只是从该查询中获取特定的用户名及其位置。
我以为只是将此查询添加到常规选择中就可以使用，但是似乎不起作用，有什么想法吗？
这将创建包含所有用户名及其位置的完整结果表，
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"
最佳答案
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"