mysql - 在 MySQL 中连接两个表

Question

我有什么:

表1

╔════╦═══════╦═════╗
║ ID ║ NAME  ║ AGE ║
╠════╬═══════╬═════╣
║  0 ║ John  ║  15 ║
║  1 ║ Chris ║  20 ║
╚════╩═══════╩═════╝

表2

╔════╦════════════╦═════════════╗
║ ID ║ OCCUPATION ║   ADDRESS   ║
╠════╬════════════╬═════════════╣
║  0 ║ Bus Driver ║ None        ║
║  1 ║ Lawyer     ║ some adress ║
╚════╩════════════╩═════════════╝

期望的输出:

表3

╔════╦═══════╦═════╦════════════╦═════════════╗
║ ID ║ NAME  ║ AGE ║ OCCUPATION ║   ADDRESS   ║
╠════╬═══════╬═════╬════════════╬═════════════╣
║  0 ║ John  ║  15 ║ Bus Driver ║ None        ║
║  1 ║ Chris ║  20 ║ Lawyer     ║ some adress ║
╚════╩═══════╩═════╩════════════╩═════════════╝

虽然我可以使用 PHP mysql_query("SELECT .. "); 来完成这个任务。 mysql_query("INSERT .."); -ish 方法，我想使用纯 SQL 来完成此任务。

Answer 1

JOIN 两个表:

SELECT
  t1.ID,
  t1.Name,
  t1.Age,
  t2.Occupation,
  t2.Address
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID;

SQL Fiddle Demo

这会给你:

| ID |  NAME | AGE | OCCUPATION |     ADDRESS |
-----------------------------------------------
|  0 |  John |  15 | Bus Driver |        None |
|  1 | Chris |  20 |     Lawyer | some adress |

---

如果您想直接从此选择创建一个新表 table3，请执行以下操作:

CREATE Table Table3
AS 
SELECT
  t1.Name,
  t1.Age,
  t2.Occupation,
  t2.Address
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID;

Like this