1. 首页
  2. 问题
  3. mysql连接2个表，2列
一尘不染

mysql连接2个表，2列

sql

假设我有

表student_data和字段是

╔════╦════════════╦════════════╗
║ ID ║ CREATED_BY ║ UPDATED_BY ║
╠════╬════════════╬════════════╣
║  1 ║          1 ║          2 ║
╚════╩════════════╩════════════╝

表creator_updater和字段是

╔════╦════════════════╗
║ ID ║      NAME      ║
╠════╬════════════════╣
║  1 ║ The Creator    ║
║  2 ║ Second Creator ║
╚════╩════════════════╝

所以我想知道确切的代码是什么，它将显示

   student_data

╔════╦═══════════════╦══════════════════════╗
║ ID ║ CREATED_BY_ID ║ LATEST_UPDATED_BY_ID ║
╠════╬═══════════════╬══════════════════════╣
║  1 ║ The Creator   ║ Second Creator       ║
╚════╩═══════════════╩══════════════════════╝

我尝试了连接语法，但似乎有所不同


阅读 177

收藏
2021-05-30

共1个答案

一尘不染

您需要将creator_updater表连接两次，以便获得两列的名称。

SELECT  a.id,
        b.name created_by_id,
        c.name latest_updated_by_id
FROM    student_data a
        INNER JOIN creator_updater b
            ON a.created_by = b.id
        INNER JOIN creator_updater c
            ON a.updated_by = c.id

但是如果其中一列为 可 为 空 ，则上面的代码将不起作用，如果是这种情况，请使用LEFT JOIN而不是INNER JOIN

SELECT  a.id,
        b.name created_by_id,
        c.name latest_updated_by_id
FROM    student_data a
        LEFT JOIN creator_updater b
            ON a.created_by = b.id
        LEFT JOIN creator_updater c
            ON a.updated_by = c.id
2021-05-30