我想根据给定用户与其他用户相比有多少点来检索用户的排名(简单地计算比给定用户多点的用户(。
但是,对于我尝试过的所有查询,我总是以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
您可以将子句上移一级,删除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
我认为下面的查询应该适合您。您可以在两个参数中传递要计算其等级的用户的user_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