由于第二级子查询嵌套而导致未知列

yizd12fk 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(380)

我想根据给定用户与其他用户相比的点数来检索用户的排名（简单地计算比给定用户点数多的用户）。
然而，在我尝试过的所有查询中，我总是以 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

sql mysql subquery correlated-subquery

来源：https://stackoverflow.com/questions/50927595/unknown-column-due-to-second-level-subquery-nesting

2条答案

按热度按时间

qeeaahzv1#

我认为下面的问题应该适合你。您可以在这两个参数中传递要计算其秩的用户的user\u id。

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

赞(0）回复(0）举报 2021-06-20

hpcdzsge2#

你可以把你的子条款提高一级，删除 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

赞(0）回复(0）举报 2021-06-20

我来回答

由于第二级子查询嵌套而导致未知列

2条答案

相关问题

热门标签

最新问答