mysql - 如何在复杂的 MySQL 查询中正确添加 WHERE 语句？

Question

我有一个非常复杂的查询来从数据库中获取一些数据，对它们进行排序并相应地排名。

这是它的 SQL fiddle :SQL Fiddle

现在我想做的是，向此查询添加一个 WHERE 语句，这样只会选择有限的用户(上面 3 个用户，下面 3 个用户，id = 8)。

WHERE sort BETWEEN @userpos - 3 AND @userpos + 3

所以它应该看起来像这样，但对于第一个示例:

SQL Fiddle

我已经尝试对此查询实现此 WHERE 语句，但我无法弄清楚应该在哪里添加，因为我总是收到错误(找不到该列)。

对我的问题有什么建议和/或解决方案吗？我应该为此重写整个查询吗？

Answer 1

如果我理解正确，您可以使用子查询来完成此操作:

SET @userid = 8

SELECT *
FROM (SELECT @pos := @pos + 1 AS sort, points, r.userid, s.active
      FROM rank r JOIN
           settings s
           USING (userid) CROSS JOIN
           (SELECT @pos := 0) p
      WHERE s.active = 1
      ORDER BY points DESC
    ) list 
WHERE userid = @userid;

请注意，这消除了您拥有的一层子查询。否则，它与您的查询非常相似。

编辑:

以上内容更多地基于 SQL Fiddle，而不是问题。 (哎呀。)

要获得给定行之前和之后的三行是可能的，只需使用技巧进行一些小调整即可。诀窍是使用用户 pos 定义另一个变量，然后在外部查询中使用该变量:

SELECT *
FROM (SELECT @pos := @pos + 1 AS sort, points, r.userid, s.active,
             if(userid = @userid, @userpos := @pos, 0)
      FROM rank r JOIN
           settings s
           USING (userid) CROSS JOIN
           (SELECT @pos := 0, @userpos := 0) p
      WHERE s.active = 1
      ORDER BY points DESC
    ) list 
WHERE `sort` between @userpos - 3 and @userpos + 3;

注意:MySQL 不保证 select 中变量的求值顺序。就执行顺序而言，以下内容更安全一些:

SELECT *
FROM (SELECT (case when (@pos := @pos + 1) is NULL then NULL
                   when (case when (userid = @userid) then @userpos := @pos else 1 end) is null
                   then NULL
                   else @pos
              end) AS sort, points, r.userid, s.active,
             if(userid = @userid, @userpos := @pos)
      FROM rank r JOIN
           settings s
           USING (userid) CROSS JOIN
           (SELECT @pos := 0, @userpos := 0) p
      WHERE s.active = 1
      ORDER BY points DESC
    ) list 
WHERE `sort` between @userpos - 3 and @userpos + 3;

奇怪的case语句是为了确保语句执行。 is null 是为了确保 when 子句失败，因此分配是按顺序进行的。