mysql - 如何在不使用子查询的情况下获得具有 max(time) 的适当列？

Question

我有下表和值:

mysql> select * from test;
+------+-------+------------+
| id   | link  | time       |
+------+-------+------------+
|    1 | link1 | 2019-03-04 |
|    1 | link2 | 2019-03-05 |
|    1 | link3 | 2019-03-06 |
|    2 | link3 | 2019-03-07 |
|    2 | link4 | 2019-03-07 |
+------+-------+------------+

我想在时间值为最大值时获取列id和link，我可以使用子查询来完成它:

mysql> select B.id, B.link from test B, (select id, max(time) as time 
       from test group by id) A where A.time = B.time and A.id = B.id;
+------+-------+
| id   | link  |
+------+-------+
|    1 | link3 |
|    2 | link3 |
|    2 | link4 |
+------+-------+

我不知道如何在不使用子查询的情况下完成它，任何建议将不胜感激!

另一个:

我想要得到:

+------+-------+
| id   | link  |
+------+-------+
|    1 | link3 |
|    2 | link3 |
+------+-------+

或者

+------+-------+
| id   | link  |
+------+-------+
|    1 | link3 |
|    2 | link4 |
+------+-------+

我该怎么办？

Answer 1

使用相关子查询

select a.* from test a
where (a.time,a.link) in(select max(time),max(link) from test b where a.id=b.id)

如果你的版本是mysql 8.0+然后使用row_number()

select * from (
select a.*,row_number()over(partition by a.id order by a.time desc) rn
 from test a
) b where b.rn=1