我有一个具有这种结构的表：
score_id | user_id | category | subcategory | score
1 | 1 | game | 0 | 100
2 | 1 | game | 0 | 200
3 | 1 | quiz | 0 | 2000
4 | 1 | quiz | 1 | 1000
5 | 1 | game | 0 | 10
6 | 1 | game | 1 | 10
7 | 1 | game | 2 | 100
8 | 1 | game | 1 | 500
9 | 2 | game | 0 | 600
我需要不同的查询mysql：
1）按user_id分组的所有团队的排名，该类别的所有最佳记录之间的所有最佳结果在该类别的所有记录与该子类别之间
预期结果
user_id | total_score | ranking
1 | 3310 | 1
2 | 600 | 2
哪里
3310 = (200 + 10 + 100+1000+2000)
200 is the best result of game 0,
10 is the best result of game 1,
100 is the best result of game 2,
2000 is the best result of quiz 0
1000 is the best result of quiz 1
600 = (600)
600 is the best result of game 0,
@Strawberry的解决方案（谢谢）
SELECT a.*
, @i:=@i+1 rank
FROM
( SELECT user_id
, SUM(subtotal) total
FROM
( SELECT user_id
, category
, subcategory
, MAX(score) subtotal
FROM my_table
GROUP
BY user_id
, category
, subcategory
) x
GROUP
BY user_id
) a
JOIN
( SELECT @i:=0) vars
ORDER
BY total DESC;
2）根据user_id分组的所有团队的排名，仅针对游戏或测验的类别和子类别的用户所有记录之间所有最佳结果的总和
预期结果游戏
user_id | total_score | ranking
2 | 600 | 1
1 | 310 | 2
哪里
600 = (600)
600 is the best result of game 0,
310 = (200 + 10 + 100)
200 is the best result of game 0,
10 is the best result of game 1,
100 is the best result of game 2,
预期成绩测验
user_id | total_score | ranking
1 | 3000 | 1
2 | 0 | 2
哪里
3000 = (200 + 10 + 100)
2000 is the best result of quiz 0
1000 is the best result of quiz 1
0= (0)
(user_id= 2 don't play quiz)
基于@Strawberry的解决方案（谢谢）
SELECT a.*
, @i:=@i+1 rank
FROM
( SELECT user_id
, SUM(subtotal) total
FROM
( SELECT user_id
, category
, subcategory
, MAX(score) subtotal
FROM my_table
WHERE category = 'game' // or 'quiz
GROUP
BY user_id
, category
, subcategory
) x
GROUP
BY user_id
) a
JOIN
( SELECT @i:=0) vars
ORDER
BY total DESC;
3）按user_id分组的所有团队的排名，仅针对具有特定子类别的游戏或测验的用户类别和子类别的所有记录之间的所有最佳结果之和
预期结果游戏0
user_id | total_score | ranking
2 | 600 | 1
1 | 310 | 2
哪里
600 = (600)
600 is the best result of game 0,
200 = (200 )
200 is the best result of game 0,
基于@Strawberry的解决方案（谢谢）
SELECT a.*
, @i:=@i+1 rank
FROM
( SELECT user_id
, SUM(subtotal) total
FROM
( SELECT user_id
, category
, subcategory
, MAX(score) subtotal
FROM my_table
WHERE category = 'game'
AND subcategory = '0'
GROUP
BY user_id
, category
, subcategory
) x
GROUP
BY user_id
) a
JOIN
( SELECT @i:=0) vars
ORDER
BY total DESC;
4）获取查询1的单个用户的总得分（ex user_id = 1）
5）获取查询1的单个用户的排名（ex user_id = 1）
6）获取查询2的单个用户的总得分（ex user_id = 1）
7）获取查询2的单个用户的排名（ex user_id = 1）
8）获取查询3的单个用户的总得分（ex user_id = 1）
9）获取查询3的单个用户的排名（ex user_id = 1）
谢谢！
最佳答案
这是第一个。使用这个，向我们展示您在其余方面的最大努力...
SELECT a.*
, @i:=@i+1 rank
FROM
( SELECT user_id
, SUM(subtotal) total
FROM
( SELECT user_id
, category
, subcategory
, MAX(score) subtotal
FROM my_table
GROUP
BY user_id
, category
, subcategory
) x
GROUP
BY user_id
) a
JOIN
( SELECT @i:=0) vars
ORDER
BY total DESC;
请注意，此解决方案不考虑联系。
关于mysql - MySQL的总和和最佳成绩排名，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45255795/