MySQl，联接/合并2个表，不带共享列，并按公共列排序 [英] MySQl, join/merge 2 tables without shared column and ordering by a common column

问题描述

我有两个表，它们具有相同的结构来处理不同的数据.我要合并它们，添加一个文本字段，指示该行的数据来自何处，并按一个公共字段排序.

I have two tables with identical structures handling distinct data. I want to merge them, add a text field indicating where the data for that row came from, and order by a common field.

TABLE1

ID|NAME|YEAR  

1,'peter',2008
2,'edward',2010

---

TABLE2

---

TABLE2

ID|NAME|YEAR

1,'compadre',2009
2,'vika',2011

---

查询的DRAFT(显然是错误的)

---

DRAFT of query ( obviously is erroneous )

select * from TABLE1 JOIN TABLE2 order by YEAR asc

预期结果:

1,'peter','iamfromTABLE1',2008
1,'compadre','iamfromTABLE2',2009
2,'edward','iamfromTABLE1',2010
2,'vika','iamfromTABLE2',2011

我知道我可以使用PHP/MySQL进行此操作，但是没有像一个简单查询"这样的更优雅的方法.

I know I can do this using PHP/MySQL, but is there not a more elegant way like the "One Simple Query".

推荐答案

使用联合查询和文字:

SELECT ID, Name, 'iamfromTABLE1' as indicator, Year
FROM Table1
UNION
SELECT ID, Name, 'iamfromTABLE2' as indicator, Year
FROM Table2
ORDER BY Year

---

as indicator在iim.hlk的推荐下添加

---

as indicator added on recommendation of iim.hlk

这篇关于MySQl，联接/合并2个表，不带共享列，并按公共列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！