MySQL-按值在单独的表中排序 [英] MySQL - Order by values in separate table

问题描述

我有两个MySQL表。
表1：

I have two MySQL tables. Table1:

id name otherid

表2：

id otherid time

在每个表中， id是每一行的唯一标识符（也是主键）
otherids表之间对应。 table2中可能有几行（或0行）具有与table1中的上位对应的上位。表

In each table, the "id" is a unique identifier for every row (and is also the primary key) the "otherids" correspond between tables. There may be several (or 0) rows in table2 that have an otherid that corresponds to an otherid in table1. Table

例如
表1：

id name otherid
1  bob  24
2  joe  326

表2：

id otherid time
4  326     2014-03-12 023:38:59
5  24      2013-02-11 22:05:21
6  24      2013-02-10 13:27:58

我希望获取table1中的每一行，并根据该主题按table2中与之相关的行的最新时间进行排序。
在我的示例中，这是我要寻找的结果：

I am looking to fetch every row in table1, ordered by the most recent time of the rows in table2 that go with it according to the otherid. In my example, this is the result I would be looking for:

id name time
2  joe  2014-03-12 023:38:59
1  bob  2013-02-11 22:05:21

这是因为Joe是table2中最近的时间。

This is because Joe has the most recent time from table2.

推荐答案

请参见以下示例：

SELECT table1.*, max(table2.time) 
FROM table1 
INNER JOIN table2 ON table1.otherid = table2.otherid
GROUP BY table2.otherid
ORDER BY table2.id

参见 SqlFiddle演示

这篇关于MySQL-按值在单独的表中排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！