查询用户在一对多表中的排名

本文介绍了查询用户在一对多表中的排名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试编写一个查询来查找用户游戏的得分排名.我需要它接收用户 ID，然后将该用户的相对排名返回到其他用户的分数.有一个用户和一个游戏桌.游戏表具有一对多关系的 userId 字段.

I am trying to write a query to find the score rank of a user's games. I need it to take in a user id and then return that user's relative ranking to other user's scores. There is a user and a game table. The game table has a userId field with a one-to-many relationship.

示例表:

用户:id freebee1 102 13

users: id freebee 1 10 2 13

游戏:用户 ID 分数1 151 202 101 15

games: userId score 1 15 1 20 2 10 1 15

将 $id 1 传递给这个函数应该返回值 1，因为用户 1 当前得分最高.同样，用户 2 将返回 2.

passing $id 1 into this function should return the value 1, as user 1 currently has the highest score. Likewise, user 2 would return 2.

目前这是我所拥有的:

SELECT outerU.id, ( SELECT COUNT( * ) FROM users userI, games gameI WHERE userI.id = gameI.userId AND userO.id = gameO.userId AND ( userI.freebee + SUM(gameI.score) ) >= ( userO.freebee + SUM(gameO.score) ) ) AS rank FROM users userO, games gameO WHERE id = $id

这给了我一个无效使用组功能"的错误.有什么想法吗?

Which is giving me an "invalid use of group function" error. Any ideas?

推荐答案

SELECT u.id,total_score, ( SELECT COUNT(*) FROM (SELECT u1.id, (IFNULL(u1.freebee,0)+ IFNULL(SUM(score),0)) as total_score FROM users u1 LEFT JOIN games g ON (g.userId = u1.id) GROUP BY u1.id )x1 WHERE x1.total_score > x.total_score )+1 as rank, ( SELECT COUNT(DISTINCT total_score) FROM (SELECT u1.id, (IFNULL(u1.freebee,0)+ IFNULL(SUM(score),0)) as total_score FROM users u1 LEFT JOIN games g ON (g.userId_Id = u1.id) GROUP BY u1.id )x1 WHERE x1.total_score > x.total_score )+1 as dns_rank FROM users u LEFT JOIN ( SELECT u1.id, (IFNULL(u1.freebee,0)+ IFNULL(SUM(score),0)) as total_score FROM users u1 LEFT JOIN games g ON (g.userId = u1.id) GROUP BY u1.id )x ON (x.id = u.id)

rank -(正常等级 - 例如 - 1,2,2,4,5)，dns_rank - 密集等级(1,2,2,3,4).列 total_score - 仅用于调试...

rank - (normal rank - e.g. - 1,2,2,4,5), dns_rank - dense rank (1,2,2,3,4). Column total_score - just for debugging...