mysql - 如何连接两个表对两个唯一列进行排序？

Question

下面有两个表格

TABLE A                                TABLE B-----------------------------------    -------------------------------|id|age|status|created_at         |    |id|height|created_at         |-----------------------------------    -------------------------------|1 |24 | 1    |2019-09-20 02:24:09|    |2 |81    |2019-09-20 02:20:15||2 |45 | 1    |2019-09-20 02:02:19|    |3 |88    |2019-09-20 02:20:50||3 |27 | 1    |2019-09-20 02:10:29|    |4 |83    |2019-09-20 02:34:00||4 |31 | 0    |2019-09-20 02:04:59|    |5 |85    |2019-09-20 02:04:49|

并希望将两个表连接在一起以生成一个按 ASC 顺序按 created_at 排序且 ID 不是 5 的表

所以基本上我想要这样的东西

TABLE C                               ------------------------------------------|id|age|status|height|created_at         |------------------------------------------|2 |45 | 1    |      |2019-09-20 02:02:19||4 |31 | 0    |      |2019-09-20 02:04:59||3 |27 | 1    |      |2019-09-20 02:10:29||2 |   |      |81    |2019-09-20 02:20:15||3 |   |      |88    |2019-09-20 02:20:50||1 |24 | 1    |      |2019-09-20 02:24:09||4 |   |      |85    |2019-09-20 02:20:50|

Answer 1

你基本上需要做 UNION ALL两个不同的表，然后对它们进行排序。此外，在这种特殊情况下，您实际上并不需要子查询:

查询

(SELECT id, age, status, NULL height, created_at 
 FROM tableA 
 WHERE id <> 5)
UNION ALL
(SELECT id, NULL, NULL, height, created_at 
 FROM tableB
 WHERE id <> 5)
-- to sort the unionized resultset
ORDER BY created_at ASC;

结果

| id  | age | status | height | created_at          |
| --- | --- | ------ | ------ | ------------------- |
| 2   | 45  | 1      |        | 2019-09-20 02:02:19 |
| 4   | 31  | 0      |        | 2019-09-20 02:04:59 |
| 3   | 27  | 1      |        | 2019-09-20 02:10:29 |
| 2   |     |        | 81     | 2019-09-20 02:20:15 |
| 3   |     |        | 88     | 2019-09-20 02:20:50 |
| 1   | 24  | 1      |        | 2019-09-20 02:24:09 |
| 4   |     |        | 83     | 2019-09-20 02:34:00 |

---

View on DB Fiddle