以下是我的表格
+----------+-----------+
| 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 |
+----------+---------+
我需要从这些表中选择一个用户获得的最高分。
这是我的MySql查询
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 |
+----------+-----------+---------+
MySql总是选择最低的id
从table_1当我使用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 |
+----------+-----------+---------+-----------+
我相信我所希望的结果是很容易得到的,但我还没有达到。
更新1
这个问题被标记为重复,但不幸的是我在给定的页面上找不到任何答案。
这是我正在尝试的查询,但它失败了。
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
它给了我错误Unknown column t1.user_id
我试图从table_2
中的score
列中获得最大值,并将user_id
中的table_1
分组。
这些页面上给出的示例只针对一个表,我无法使其在我的场景中工作
编写获取每个user_id
的最大分数的子查询。然后将其与您的表连接以获得具有该最大值的行。
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
演示