mysql - 如何获取 MIN() 选择的行的更多数据

Question

我有一张这样的桌子：

// mytable
+----+----------+--------------+
| id |  user_id |  reputation  |
+----+----------+--------------+
| 1  | 442      | 1000         |
| 2  | 746      | 500          |
| 3  | 843      | 800          |
| 4  | 746      | 700          |
| 5  | 442      | 300          |
+----+----------+--------------+

这是我的查询：

select user_id, min(reputation) min_rep
from mytable 
group by user_id

结果如下：

+---------+---------+
| user_id | min_rep |
+---------+---------+
| 442     | 300     |
| 746     | 500     |
| 843     | 800     |
+---------+---------+

现在我需要选择该id行的。所以这是预期的结果：

+----+---------+---------+
| id | user_id | min_rep |
+----+---------+---------+
| 5  | 442     | 300     |
| 2  | 746     | 500     |
| 3  | 843     | 800     |
+----+---------+---------+

知道我该怎么做吗？

标签： mysqlsql

Answer 1

试试下面的方法——

select * from 
from mytable a where reputation = 
   (select min(reputation) from mytable b where a.userid=b.userid group by b.userid )