mysql - 如果主键与第三个表匹配并按第三个表排序，我将如何连接两个表？

Question

如果两个表与第三个表的主键匹配，我将尝试连接两个具有相同结构的表。然后我想根据第三个表中的列进行排序。

结构示例:

 Table 1 - id (VARCHAR) | title (VARCHAR) | expire (TIMESTAMP)

 Table 2 - id (VARCHAR) | title (VARCHAR) | expire (TIMESTAMP)

 Table 3 - id (VARCHAR) | views (VARCHAR) | expire (TIMESTAMP)

我认为这应该有效，但事实并非如此:

 SELECT * FROM table1 as t, table3 as t3 WHERE t.id = t3.id
 UNION
 SELECT * FROM table2 as t2, table3 as t3 WHERE t2.id = t3.id
 ORDER BY table3.expire DESC

什么可以实现这个目标？

Answer 1

通过 JOIN 将表 3 中的排序列引入，然后对其进行排序。

SELECT table_1.*, table_3.expire as t3_expire
  FROM table_1
  INNER JOIN table_3 on table_1.id = table_3.id
UNION ALL
SELECT table_2.*, table_3.expire
  FROM table_2
  INNER JOIN table_3 on table_2.id = table_3.id
ORDER BY t3_expire DESC;