用户维度获取分值最高的记录 - 《MySQL》

用户分值表 user_socre | id | user_id | score | month | color | | —- | —- | —- | —- | —- | | 1 | 101 | 80 | 1 | blue | | 2 | 102 | 55 | 1 | red | | 3 | 103 | 65 | 1 | green | | 4 | 103 | 75 | 2 | green | | 5 | 102 | 44 | 2 | red | | 6 | 101 | 82 | 2 | blue | | 7 | 105 | 92 | 1 | yellow | | 8 | 101 | 95 | 3 | yellow | | 9 | 102 | 77 | 2 | green | | | | | | |

表结构如图，结果需求：以user_id为维度，(1) 取score最高的一条记录的score (2) 取score最高记录所在行

(1) 以user_id为维度，取score最高的一条记录的score
SELECT
user_id, MAX(score) AS max_score
FROM user_socre
GROUP BY user_id

(2) 以user_id为维度，取score最高记录所在行

SELECT 
    user_id, 
    SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY score DESC), ',', 1) AS max_score_id
FROM user_socre
GROUP BY user_id