mysql连接2个表，2列

Question

假设我有

表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       ║╚════╩═══════════════╩══════════════════════╝

我试过连接语法，但似乎不一样

Answer 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

• SQLFiddle Demo

但如果其中一列是可空，上面的代码将不起作用，如果是这种情况，请使用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

• SQLFiddle Demo

有关连接的更多信息

• Visual Representation of SQL Joins