如何在同一SQL查询中引用生成/别名表？附排行榜场景示例

嘿，我来帮你搞定这个SQL查询的问题！你之前的查询失败，核心原因是在同一个SELECT语句中，无法直接引用外层子查询的别名——SQL解析器在处理WHERE子句的时候，generated_ordered_table这个临时表还没被完全实例化，所以没法直接用它的列来做过滤条件。

下面给你几种可行的解决方案，适配不同的MySQL版本：

方法1：用CTE（公共表表达式，MySQL 8.0+推荐） #

CTE是最清晰的写法，它会先把有序表定义好，整个查询中都可以直接引用这个别名：

WITH generated_ordered_table 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 generated_ordered_table
WHERE row_number BETWEEN 
    GREATEST((SELECT row_number FROM generated_ordered_table WHERE userId = 1) - 4, 1)
    AND 
    (SELECT row_number + 4 FROM generated_ordered_table WHERE userId = 1)
ORDER BY row_number ASC;

这里用GREATEST()是为了处理边界情况：如果目标用户的排名太靠前（比如第1名），row_number -4会变成负数，GREATEST()会自动把下限设为1，避免返回不存在的行。

方法2：用窗口函数替代用户变量（MySQL 8.0+更可靠） #

如果你用的是MySQL 8.0及以上版本，推荐用窗口函数ROW_NUMBER()代替用户变量，它的行为更稳定，写法也更简洁：

WITH generated_ordered_table AS (
    SELECT 
        userId, 
        rank, 
        score, 
        createdAt,
        ROW_NUMBER() OVER (ORDER BY rank ASC) AS row_number
    FROM `predictions`
    WHERE gameId = 18
)
SELECT * 
FROM generated_ordered_table
WHERE row_number BETWEEN 
    GREATEST((SELECT row_number FROM generated_ordered_table WHERE userId = 1) - 4, 1)
    AND 
    (SELECT row_number + 4 FROM generated_ordered_table WHERE userId = 1)
ORDER BY row_number ASC;

窗口函数会自动按rank排序并生成行号，不需要手动维护用户变量，减少出错概率。

方法3：适配老版本MySQL（无CTE支持） #

如果你的MySQL版本低于8.0，不支持CTE，可以把目标行号提前查询出来，或者嵌套子查询：

方案3.1：先获取目标行号，再查询 #

-- 先获取目标用户的行号
SET @target_row = (
    SELECT row_number
    FROM (
        SELECT 
            l.userId, 
            @curRow := @curRow + 1 AS row_number
        FROM (SELECT * FROM `predictions` WHERE gameId = 18) l
        JOIN (SELECT @curRow := 0) r
        ORDER BY rank ASC
    ) t
    WHERE userId = 1
);

-- 再查询上下4名用户
SELECT * 
FROM (
    SELECT 
        l.userId, 
        l.rank, 
        l.score, 
        l.createdAt, 
        @curRow2 := @curRow2 + 1 AS row_number
    FROM (SELECT * FROM `predictions` WHERE gameId = 18) l
    JOIN (SELECT @curRow2 := 0) r
    ORDER BY rank ASC
) generated_ordered_table
WHERE row_number BETWEEN GREATEST(@target_row -4, 1) AND @target_row +4
ORDER BY row_number ASC;

方案3.2：合并成单个查询（嵌套子查询） #

如果不想用变量，可以把行号查询嵌套两次：

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 BETWEEN 
    GREATEST(
        (SELECT row_number FROM (
            SELECT 
                l.userId, 
                @curRow2 := @curRow2 + 1 AS row_number
            FROM (SELECT * FROM `predictions` WHERE gameId = 18) l
            JOIN (SELECT @curRow2 := 0) r
            ORDER BY rank ASC
        ) t WHERE userId = 1) -4, 1
    )
    AND
    (SELECT row_number FROM (
        SELECT 
            l.userId, 
            @curRow3 := @curRow3 + 1 AS row_number
        FROM (SELECT * FROM `predictions` WHERE gameId = 18) l
        JOIN (SELECT @curRow3 := 0) r
        ORDER BY rank ASC
    ) t WHERE userId = 1) +4
ORDER BY row_number ASC;

这种写法虽然冗长，但能在老版本MySQL中正常运行。

内容的提问来源于stack exchange，提问作者David