合并两个具有相同列名的MYSQL表
问题描述：
我有两个桌子
表1是计划时间
id | edition | time |
1 | 1 | 9:23am |
2 | 2 | 10:23am|
表二是实际时间
id | edition | time |
1 | 1 | 10:23am |
2 | 2 | 11:23am |
我想作为
Caption | Edition | Time |
Scheduleed | 1 | 9:23am |
actual | 1 | 10:23am |
Scheduleed | 2 | 10:23am |
actual | 2 | 11:23am |
如何在MySQL中做到这一点?
How can do this in MySQL ?
答
SELECT Caption, Edition, Time
FROM
(
SELECT 'Scheduled' Caption, Edition, time
FROM scheduleTime
UNION ALL
SELECT 'Actual' Caption, Edition, time
FROM scheduleTime
) subquery
ORDER BY Edition, FIELD(Caption, 'Scheduled', 'Actual')
- SQLFiddle演示
- SQLFiddle演示(，不使用
FIELD()，仅使用ORDER BY...DESC) - SQLFiddle Demo
- SQLFiddle Demo (without using
FIELD(), just plainORDER BY...DESC)
输出
╔═══════════╦═════════╦═════════╗
║ CAPTION ║ EDITION ║ TIME ║
╠═══════════╬═════════╬═════════╣
║ Scheduled ║ 1 ║ 9:23am ║
║ Actual ║ 1 ║ 9:23am ║
║ Scheduled ║ 2 ║ 10:23am ║
║ Actual ║ 2 ║ 10:23am ║
╚═══════════╩═════════╩═════════╝