从 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
.
我也尝试过加入:
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?
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 | 姓名 |
---|---|---|
1 | 10 | row1 |
4 | 5 | row2 |
参见这个SQLFiddle
- 具有更多价值的演示
- 带有重复值的演示
- 删除重复值的演示(使用
DISTINCT
)
这里我用 minVal 和 Name 自行加入了表.
Here I have self-joined the table with minVal and Name.
相关文章