在第一个表上分组，并保持第二个表的最大值 [英] Group by on first table and keep the highest value of the second table

问题描述

这是我的桌子

+----------+-----------+
|   id     |  user_id  |
+----------+-----------+
|    1     |     1     |
+----------+-----------+
|    2     |     1     |
+----------+-----------+
|    3     |     1     |
+----------+-----------+
|    4     |     2     |
+----------+-----------+
|    5     |     2     |
+----------+-----------+
|    6     |     2     |
+----------+-----------+
|    7     |     3     |
+----------+-----------+
|    8     |     3     |
+----------+-----------+
|    9     |     3     |
+----------+-----------+

我的第二张桌子

+----------+---------+
|   id     |  score  |
+----------+---------+
|    1     |    10   |
+----------+---------+
|    2     |    20   |
+----------+---------+
|    3     |     5   |
+----------+---------+
|    4     |    40   |
+----------+---------+
|    5     |    15   |
+----------+---------+
|    6     |    10   |
+----------+---------+
|    7     |     5   |
+----------+---------+
|    8     |    30   |
+----------+---------+
|    9     |    10   |
+----------+---------+

我需要从这些表中选择用户获得的最高分数.

I need to select the highest score achieved by a user from these tables.

这是我的MySql查询

Here is my MySql query

SELECT * FROM 
table_1 AS t1
INNER JOIN 
table_2 AS t2 ON 
t1.id = t2.id 
WHERE t2.score > 10
GROUP BY t1.user_id
ORDER BY t2.score DESC

我的愿望结果是

+----------+-----------+---------+
|   id     |  user_id  |  score  |
+----------+-----------+---------+
|    4     |      2    |    40   |
+----------+-----------+---------+
|    8     |      3    |    30   |
+----------+-----------+---------+
|    2     |      1    |    20   |
+----------+-----------+---------+

但是我得到的是

+----------+-----------+---------+
|   id     |  user_id  |  score  |
+----------+-----------+---------+
|    4     |      2    |    40   |
+----------+-----------+---------+
|    1     |      1    |    10   |
+----------+-----------+---------+
|    7     |      3    |     5   |
+----------+-----------+---------+

当我使用 GROUP BY 子句

我尝试使用 MAX 这样的命令

SELECT *, MAX(t2.score) AS max_score FROM 
table_1 AS t1
INNER JOIN 
table_2 AS t2 ON 
t1.id = t2.id 
WHERE t2.score > 10
GROUP BY t1.user_id
ORDER BY t2.score DESC
LIMIT 10

我得到的结果

+----------+-----------+---------+-----------+
|   id     |  user_id  |  score  | max_score |
+----------+-----------+---------+-----------+
|    4     |      2    |    40   |    40     |
+----------+-----------+---------+-----------+
|    1     |      1    |    10   |    20     |
+----------+-----------+---------+-----------+
|    7     |      3    |     5   |    30     |
+----------+-----------+---------+-----------+

我相信我希望很容易获得结果，但是我无处可寻.

I believe the result I wish it quite easy to get but I'm nowhere there.

更新1

此问题被标记为重复，但是很遗憾，在给定的页面上我找不到任何解决方案.

This question was marked duplicate but unfortunately I couldn't find any solution on that given page.

这是我正在尝试的查询，但失败.

Here is the query that I'm trying but it fails.

SELECT * AS max_score FROM 
table_1 AS t1
INNER JOIN 
(
SELECT *, MAX(score) AS max_score
FROM table_2
GROUP BY t1.user_id
) AS t2
ON 
t1.id = t2.id 
WHERE t2.score > 10
ORDER BY t2.score DESC
LIMIT 10

它给我错误 t1.user_id未知列

我正在尝试从 table_2 中的 score 列中获取最大值，并根据 user_id 中的结果对结果进行分组 table_1 .

I'm trying to get the highest value from the column score which is in table_2 and group the results by user_id which is in table_1.

这些页面上给出的示例仅针对一张桌子，我想让它适用于我的情况.

The examples given on those pages target only one table and I'cant make that work on my scenario.

推荐答案

编写一个子查询，该子查询获取每个 user_id 的最高分.然后将其与表格结合在一起，以获取具有最高得分的行.

Write a subquery that gets the max score for each user_id. Then join that with your tables to get the rows with that max score.

SELECT t1.id, t1.user_id, max_score
FROM table_1 AS t1
JOIN table_2 AS t2 ON t1.id = t2.id
JOIN (
    SELECT t1.user_id, MAX(t2.score) AS max_score
    FROM table_1 AS t1
    JOIN table_2 AS t2 on t1.id = t2.id
    GROUP BY t1.user_id) AS t_max
ON t1.user_id = t_max.user_id AND t2.score = t_max.max_score

演示

这篇关于在第一个表上分组，并保持第二个表的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！