合并两个具有相同列名的MYSQL表 [英] Combine two MYSQL table with same column Name

问题描述

我有两个桌子

表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 plain ORDER BY...DESC)

输出

╔═══════════╦═════════╦═════════╗
║  CAPTION  ║ EDITION ║  TIME   ║
╠═══════════╬═════════╬═════════╣
║ Scheduled ║       1 ║ 9:23am  ║
║ Actual    ║       1 ║ 9:23am  ║
║ Scheduled ║       2 ║ 10:23am ║
║ Actual    ║       2 ║ 10:23am ║
╚═══════════╩═════════╩═════════╝

这篇关于合并两个具有相同列名的MYSQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！