I have two table
table one is scheduletime
id | edition | time |
1 | 1 | 9:23am |
2 | 2 | 10:23am|
table two is actualtime
id | edition | time |
1 | 1 | 10:23am |
2 | 2 | 11:23am |
I want to result as
Caption | Edition | Time |
Scheduleed | 1 | 9:23am |
actual | 1 | 10:23am |
Scheduleed | 2 | 10:23am |
actual | 2 | 11:23am |
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')
OUTPUT
╔═══════════╦═════════╦═════════╗
║ CAPTION ║ EDITION ║ TIME ║
╠═══════════╬═════════╬═════════╣
║ Scheduled ║ 1 ║ 9:23am ║
║ Actual ║ 1 ║ 9:23am ║
║ Scheduled ║ 2 ║ 10:23am ║
║ Actual ║ 2 ║ 10:23am ║
╚═══════════╩═════════╩═════════╝