我试图在执行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)
为了获得最低的值(value),我将使用
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
。我也尝试了加入:
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)
如何根据最低的
value
是每个结果的正确ID? 最佳答案
我认为这是您要实现的目标:
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
值
名称
1个
10
第1行
4
5
第2行
参见this SQLFiddle
DISTINCT
) 在这里,我使用minVal和Name自联接了表。
关于mysql - 从GROUP BY获取具有最高或最低值的行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/16910050/