mysql - 二级子查询嵌套导致未知列

Question

我想根据给定用户与其他用户相比得到的分数来检索用户的排名(简单地计算比给定用户拥有更多分数的用户)。

但是，在我尝试过的所有查询中，我总是以 Column not found: users.id 结束。据我所知，引用相关父列的级别超过一个级别是有限制的。

我可以重构我的查询，还是我真的需要使用 SET @rownum := 0 风格的查询？

SELECT 
    `users`.*, 
    (
        SELECT COUNT(*) + 1 
        FROM (
            SELECT SUM(`amount`) AS `all_points` 
            FROM `points` 
            WHERE `type` = ? 
            GROUP BY `user_id` 
            HAVING `all_points` > (
                SELECT SUM(`amount`) 
                FROM `points` 
                WHERE `type` = ? and `user_id` = `users`.`id`
            )
        ) `points_sub`
    ) as `rank` 
FROM `users` 
WHERE `users`.`id` = ? 
LIMIT 1

Answer 1

您可以将子句上移一个级别，删除 having 过滤器并使用 where 过滤器

SELECT 
    `users`.*, 
    (
        SELECT COUNT(*) + 1 
        FROM (
            SELECT user_id,SUM(`amount`) AS `all_points` 
            FROM `points` 
            WHERE `type` = ? 
            GROUP BY `user_id` 
        ) `points_sub`
        WHERE `all_points` > 
                SELECT SUM(`amount`) 
                FROM `points` 
                WHERE `type` = ? and `user_id` = `users`.`id`

    ) as `rank` 
FROM `users` 
WHERE `users`.`id` = ?
LIMIT 1