mysql - 如何在同一查询中引用生成/别名表？

Question

我想要查找用户在排行榜中的位置，并返回其位置上方的 4 位用户和下方的 4 位用户。

我的表“预测”看起来像这样:

+----+---------+--------+-------+---------+
| id |  userId |  score |  rank |  gameId |
+----+---------+--------+-------+---------+
|  1 |      12 |     11 |     1 |      18 |
|  2 |       1 |      6 |     4 |      18 |
|  3 |      43 |      7 |     3 |      12 |
|  4 |       4 |      9 |     2 |      18 |
|  5 |      98 |      2 |     5 |      19 |
|  6 |       3 |      0 |     6 |      18 |
+----+---------+--------+-------+---------+

显然这没有正确排序，所以我运行这个:

SELECT  l.userId,
        l.rank,
        l.score,
        l.createdAt,
        @curRow := @curRow + 1 AS row_number
FROM    (SELECT * FROM `predictions` WHERE gameId = 18) l
JOIN    (SELECT @curRow := 0) r
ORDER BY rank ASC

这给我带来了一张漂亮的表格，每个条目都编号了。

然后我想搜索这个生成的表，找到 userId = X 的 row_number，然后返回该结果“周围”的值。

我认为我已经掌握了查询的逻辑，我只是​​不知道如何引用上述查询“生成”的表。

它会是这样的:

SELECT *
FROM (
SELECT  l.userId,
        l.rank,
        l.score,
        l.createdAt,
        @curRow := @curRow + 1 AS row_number
FROM    (SELECT * FROM `predictions` WHERE gameId = 18) l
JOIN    (SELECT @curRow := 0) r
ORDER BY rank ASC) generated_ordered_table
WHERE row_number < (SELECT row_number FROM generated_ordered_table WHERE userId = 1)
ORDER BY row_number DESC
LIMIT 0,5

这失败了。我想做的是使用正确的查询生成第一个表，为其指定一个 generated_ordered_table 的别名，然后稍后在此查询中引用此“表”。

我该怎么做？

Answer 1

MySQL 版本 8+ 可能允许使用 Window functions ，和 Common Table Expressions (CTEs) ;这会大大简化查询。

现在，在旧版本(您的情况)中，无法在 WHERE 子句内的子查询中再次引用“生成的排名表”( Derived Table )。一种方法是在子查询中再次执行相同的操作两次(select 子句以获取生成的表)，但这相对效率较低。

因此，另一种方法可以是使用 Temporary Tables 。我们首先创建一个临时表来存储排名。然后引用该临时表来获取相应的结果:

CREATE TEMPORARY TABLE IF NOT EXISTS gen_rank_tbl AS
(SELECT l.userId,
        l.rank,
        l.score,
        l.createdAt,
        @curRow := @curRow + 1 AS row_number
 FROM   (SELECT * FROM `predictions` WHERE gameId = 18) l
 JOIN   (SELECT @curRow := 0) r
 ORDER BY rank ASC)

现在，您可以引用此临时表来获得所需的结果:

SELECT *
FROM gen_rank_tbl 
WHERE row_number < (SELECT row_number FROM gen_rank_tbl WHERE userId = 1)
ORDER BY row_number DESC
LIMIT 0,5