Mysql选择查询计数，直到达到条件条件 [英] Mysql select query count until reach the condition with condition
问题描述
我有他的积分和游戏ID的用户列表。我需要通过max（lb_point）基于游戏顺序来找到指定用户的排名。
I have lists of users with his points and game id. I need to find the rank of the specified user based on the game order by the max(lb_point).
我已经完成了基于个人的排名的查询游戏如下。
I have already done the query for getting the rank based on individual game as follows.
select count(*) AS user_rank
from (
select distinct user_id
from leader_board
where lb_point >= (select max( lb_point )
from leader_board
where user_id = 1
and game_id = 2 )
and game_id = 2
) t
但是我需要根据整体游戏找到排名。示例i有3个不同的游戏（1,2,3）。通过传递user_id，我需要找到他在所有三个游戏中的总体排名。你能帮我一下吗？
But i need to find the rank based on the overall game. Example i have 3 different games (1,2,3). By passing the user_id, i need to find his overall rank among all three games. Can you please help me on this?
lb_id user_id game_id lb_point
------------------------------------------------
1 1 2 670
2 1 1 200
3 1 2 650
4 1 1 400
5 3 2 700
6 4 2 450
7 2 1 550
8 2 1 100
9 1 1 200
10 2 1 100
11 1 1 200
12 2 1 100
13 1 1 200
14 2 1 100
15 1 1 200
16 2 1 100
17 1 1 200
18 2 1 100
19 1 1 200
20 2 1 100
21 1 1 200
22 2 1 800
推荐答案
use sandbox;
/*create table t (lb_id int, user_id int, game_id int, lb_point int);
truncate table t;
insert into t values
(1 , 1, 2, 670),
(2 , 1, 1, 200),
(3 , 1, 2, 650),
(4 , 1, 1, 400),
(5 , 3, 2, 700),
(6 , 4, 2, 450),
(7 , 2, 1, 550),
(8 , 2, 1, 100),
(9 , 1, 1, 200),
(10, 2, 1, 100),
(11, 1, 1, 200),
(12, 2, 1, 100),
(13, 1, 1, 200),
(14, 2, 1, 100),
(15, 1, 1, 200),
(16, 2, 1, 100),
(17, 1, 1, 200),
(18, 2, 1, 100),
(19, 1, 1, 200),
(20, 2, 1, 100),
(21, 1, 1, 200),
(22, 2, 1, 800);
*/
select t.*
from
(
select s.*,@rn:=@rn+1 as rank
from
(
select user_id, sum(lb_point) points
from t
where lb_id = (select t1.lb_id from t t1 where t1.user_id = t.user_id and t1.game_id = t.game_id order by t1.lb_point desc limit 1)
group by user_id
order by points desc
) s
,(select @rn:=0) rn
) t
where t.user_id = 1
最内层查询获取每个用户的每个游戏的最高分数并将其相加。
下一个查询根据每个用户的聚合分数分配排名。
最外面的查询选择用户。
The innermost query grabs the highest score per game per user and sums it. The next query assigns a rank based on the aggregated score per user. The outermost query selects the user.
这篇关于Mysql选择查询计数，直到达到条件条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
