mysql - Sql 按其他表选择顺序

Question

我想按“contactreply”表对“contact”表进行排序。“contactreply”表中的“contactid”行是“contact”表中的“ID”行。 “Contact”必须按表“contactreply”中的最后一个插入进行排序，其中“contactid”是表“contact”中的“ID”。

联系表:

+----+--------+----------+----------+
| ID | userid | subject  | content  |
+----+--------+----------+----------+
|  1 |      1 | subject  | subject  |
|  2 |      1 | subject2 | subject2 |
+----+--------+----------+----------+

回复表

+----+--------+-----------+---------+
| ID | userid | contactid | content |
+----+--------+-----------+---------+
|  1 |      1 |         1 | reply1  |
|  2 |      1 |         2 | reply2  |
|  3 |      1 |         1 | fasd    |
|  4 |      1 |         2 | asdf    |
|  5 |      1 |         2 | f       |
|  6 |      1 |         1 | asdf    |
+----+--------+-----------+---------+

我尽力解释它并希望你理解它；)我已经看过其他帖子，但我无法让它工作。

提前谢谢您。

Answer 1

SELECT c.* 
  FROM contacts c 
  JOIN reply x 
    ON x.contactid = c.id 
  JOIN 
     ( SELECT MAX(id) max_id FROM reply GROUP BY contactid ) y 
    ON y.max_id = x.id 
 ORDER 
    BY x.id;
+----+--------+----------+----------+
| ID | userid | subject  | content  |
+----+--------+----------+----------+
|  2 |      1 | subject2 | subject2 |
|  1 |      1 | subject  | subject  |
+----+--------+----------+----------+