查询用户在一对多表中的排名 [英] querying for user's ranking in one-to-many tables
问题描述
我正在尝试编写一个查询来查找用户游戏的得分排名.我需要它接收用户 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 freebee
1 10
2 13
users:
id freebee
1 10
2 13
游戏:
用户 ID 分数
1 15
1 20
2 10
1 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...
这篇关于查询用户在一对多表中的排名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!