我有两个表如下所示
表A表B ----------------------------------- ----------- -------------------- | id | age |状态| 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排序的表
所以基本上我想要这样的东西
表C ------------------------------------------ | id | age |状态| 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 |
基本上,您需要做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 |
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句