mysql - 将表合并为 1，并带有指示相应表条目的标志

Question

我有 2 个表。

1.home_shop

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

2.office_shop

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

我想要的是将这两个表合并到一个新表“complete_shop”中，并带有一个表示“home”和“office”的标志例如:

+---+------------+-------------+
|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

请注意，您无需使用子查询来进行此处排序，您只需指定要使用的列即可。