合并具有不同列的两个表mysql [英] merge two tables with different columns mysql
本文介绍了合并具有不同列的两个表mysql的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想合并两个不同列的表 mysql.例如
I want to merge two tables with different columns mysql. For e.g.
表一:
-------------------------------------------------------------
item_id title slug type views updatedAt createdAt
-------------------------------------------------------------
1 sometitle someslg 1 43454 timestamp timestamp
2 sometitle someslg 1 43566 timestamp timestamp
表 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 合并.这是我的查询
The above tables are examples. I have merged with UNION. Here is my query
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
这工作正常执行查询后,我会得到类似的结果
This is working fine After Executing the query i will get the results like
-------------------------------------------------------------
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
但我希望结果中有一个虚拟列,显示如下表中的行
But i want a virtual column in the result that shows the row from which table as shown below
--------------------------------------------------------------------------
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
推荐答案
只需将列添加到:
SELECT t3.*
FROM ((SELECT t1.item_id, t1.title, t1.slug, t1.type, t1.views, t1.updatedAt, t1.createdAt, 't1' as from_tbl
FROM table1 t1
) UNION ALL
(SELECT t2.id, t2.ptitle, t2.slug, '', t2.pviews, t2.updatedAt, t2.createdAt, 't2'
)
) t3
ORDER BY t3.item_id ASC
这篇关于合并具有不同列的两个表mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文