将多个表合并为1，并带有指示相应表项的标志

Question

我有两张桌子。

1.home_shop

+---+------------+
|id | product    |   
+---+------------+
| 1 | soap       | 
| 2 | cake       | 
| 3 | biscuit    | 
+---+------------+

2.office_shop

+---+------------+
|id | product    |   
+---+------------+
| 1 | key        | 
| 2 | lock       | 
| 3 | pen        | 
+---+------------+

我想要的是将这两个表合并到一个新的表"complete_shop“中，并带有一个指示”家“和”办公室“的标志，例如：

+---+------------+-------------+
|id | product    |    flag     |
+---+------------+-------------+
| 1 | soap       | home        |
| 1 | key        | office      |
| 2 | cake       | home        |
| 2 | lock       | office      |
| 3 | biscuit    | home        |
| 3 | pen        | office      |
+---+------------+-------------+

我如何在mysql中做这个联合，请帮帮我。我是个初学者

Answer 1

执行UNION查询并使用适当的值引入flag列。

SELECT id, product, 'home' AS flag
FROM home_shop
UNION ALL
SELECT id, product, 'office' AS flag
FROM office_shop
ORDER BY id, flag

请注意，这里不需要使用子查询进行排序，只需指定要使用的列即可。