本文介绍了从GROUP BY获取最高或最低值的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图在执行 GROUP BY 后获得最高/最低编号的行:
I'm trying to get the row with the highest/lowest number, after performing a GROUP BY:
这是我的测试数据
mysql> SELECT * FROM test; +----+-------+------+ | id | value | name | +----+-------+------+ | 1 | 10 | row1 | | 2 | 12 | row2 | | 3 | 10 | row2 | | 4 | 5 | row2 | +----+-------+------+ 4 rows in set (0.00 sec)为了得到最小值,我将使用 MIN()
To get the lowest value, I'll use MIN()
mysql> SELECT id, name, MIN(value) AS value FROM test GROUP BY name; +----+------+-------+ | id | name | value | +----+------+-------+ | 1 | row1 | 10 | | 2 | row2 | 5 | +----+------+-------+ 2 rows in set (0.00 sec)现在,id row2 是 2 ,但它应该是 4 。
Now, the id row2 is 2, but it should be 4.
我也尝试了一个连接:
I also tried with a join:
mysql> SELECT t1.* FROM (SELECT id, name, MIN(value) AS value FROM test GROUP BY name) AS t1 INNER JOIN test AS t2 ON t1.id = t2.id; +----+------+-------+ | id | name | value | +----+------+-------+ | 1 | row1 | 10 | | 2 | row2 | 5 | +----+------+-------+ 2 rows in set (0.00 sec)如何根据最低值的结果得到每个结果的正确ID?
How can I get the correct ID for each result based on what the lowest value is?
推荐答案我认为这就是您要实现的目标:
I think this is what you are trying to achieve:
SELECT t.* FROM test t JOIN ( SELECT Name, MIN(Value) minVal FROM test GROUP BY Name ) t2 ON t.Value = t2.minVal AND t.Name = t2.Name;输出:
╔════╦═══════╦══════╗ ║ ID ║ VALUE ║ NAME ║ ╠════╬═══════╬══════╣ ║ 1 ║ 10 ║ row1 ║ ║ 4 ║ 5 ║ row2 ║ ╚════╩═══════╩══════╝请参阅 this SQLFiddle
- 具有更多价值的演示
- 具有重复值的演示
- 删除重复值的演示(使用 DISTINCT )
- Demo with more values
- Demo with duplicate values
- Demo with removing duplicate values (using DISTINCT)
See this SQLFiddle
这里我用minVal和Name自加入表。
Here I have self-joined the table with minVal and Name.
更多推荐
从GROUP BY获取最高或最低值的行
发布评论