本文介绍了在MySQL中为GROUP获取MAX行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下资料:
I have the following data:
+---------+----------+----------+--------+
| id | someId | number | data |
+---------+----------+----------+--------+
| 27 | 123 | 1 | abcde1 |
| 28 | 123 | 3 | abcde2 |
| 29 | 123 | 1 | abcde3 |
| 30 | 123 | 5 | abcde4 |
| 31 | 124 | 4 | abcde1 |
| 32 | 124 | 8 | abcde2 |
| 33 | 124 | 1 | abcde3 |
| 34 | 124 | 2 | abcde4 |
| 35 | 123 | 16 | abcde1 |
| 245 | 123 | 3 | abcde2 |
| 250 | 125 | 0 | abcde3 |
| 251 | 125 | 1 | abcde4 |
| 252 | 125 | 7 | abcde1 |
| 264 | 125 | 0 | abcde2 |
| 294 | 123 | 0 | abcde3 |
| 295 | 126 | 0 | abcde4 |
| 296 | 126 | 0 | abcde1 |
| 376 | 126 | 0 | abcde2 |
+---------+----------+----------+--------+
我想得到一个MySQL查询,该查询获取行的 data 每个 someId 的最高数字。请注意, id 是唯一的,但 number 不是
And I want to get a MySQL query that gets me the data of the row with the highest number for each someId. Note that id is unique, but number isn't
推荐答案
对Naeel的答案稍作修改,但对于任何someId只返回单个数据结果,即使存在领带也应该添加 GROUP BY
:
Slight tweak to Naeel's answer but to return just a single data result for any someId even if there's a tie you should add a GROUP BY
:
SELECT t1.someid, t1.number, t1.data
FROM Table1 t1
INNER JOIN (SELECT someId sid, max(number) max_number
FROM Table1
GROUP BY someId) t2
ON (someId = sid AND number = max_number)
GROUP BY t1.someId
SQL小提琴此处
这篇关于在MySQL中为GROUP获取MAX行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!