我想合并两个具有不同列的表mysql。
例如
表格1 :
-------------------------------------------------------------
item_id title slug type views updatedAt createdAt
-------------------------------------------------------------
1 sometitle someslg 1 43454 timestamp timestamp
2 sometitle someslg 1 43566 timestamp timestamp
Table 2:
-------------------------------------------------------------
id ptitle slug pviews updatedAt createdAt
-------------------------------------------------------------
1 sometitle someslg 3434 timestamp timestamp
2 sometitle someslg 6454 timestamp timestamp
3 sometitle someslg 5454 timestamp timestamp
上表是示例。
我已经与UNION。这是我的查询
SELECT * ((SELECT t1.item_id,t1.title,t1.slug,t1.type,t1.views,t1.updatedAt,t1.createdAt
FROM table1
t1) UNION ALL (SELECT t2.id,t2.ptitle,t2.slug,'',t2.pviews,t2.updatedAt,t2.createdAt)) t3
ORDER BY t3.item_id ASC
这工作正常执行查询后我会得到如下结果
-------------------------------------------------------------
item_id title slug type views updatedAt createdAt
-------------------------------------------------------------
1 sometitle someslg 1 43454 timestamp timestamp
1 sometitle someslg 3434 timestamp timestamp
2 sometitle someslg 1 43566 timestamp timestamp
2 sometitle someslg 6454 timestamp timestamp
3 sometitle someslg 5454 timestamp timestamp
但我想要结果中的虚拟列显示哪个表中的行,如下所示
--------------------------------------------------------------------------
item_id title slug type views updatedAt createdAt from_tbl
--------------------------------------------------------------------------
1 sometitle someslg 1 43454 timestamp timestamp t1
1 sometitle someslg 3434 timestamp timestamp t2
2 sometitle someslg 1 43566 timestamp timestamp t1
2 sometitle someslg 6454 timestamp timestamp t2
3 sometitle someslg 5454 timestamp timestamp t2