mysql连接2个表，2列 [英] mysql join 2 tables, 2 columns

问题描述

假设我有

表 student_data 和字段是

<前>╔====╦===========╦===========╗║ ID ║ CREATED_BY ║ UPDATED_BY ║╠====╬===========╬===========╣║ 1 ║ 1 ║ 2 ║╚====╩===========╩===========╝

表 creator_updater 和字段是

<前>╔====╦===============╗║ 身份证 ║ 姓名 ║╠＝＝＝＝╬＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝╣║ 1 ║ 造物主 ║║ 2 ║ 第二个创造者 ║╚====╩===============╝

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

<前>学生数据╔＝＝＝＝╦＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝╦＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝╗║ ID ║ CREATED_BY_ID ║ LATEST_UPDATED_BY_ID ║╠＝＝＝＝╬＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝╬＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝╣║ 1 ║ 造物主 ║ 第二造物主 ║╚＝＝＝＝╩＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝╩＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝╝

我尝试过连接语法，但似乎不同

解决方案

您需要加入 creator_updater 表两次才能获得两列的名称.

SELECT a.id,b.name created_by_id,c.name latest_updated_by_idFROM student_data aINNER JOIN creator_updater bON a.created_by = b.idINNER JOIN creator_updater cON a.updated_by = c.id

• SQLFiddle 演示

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

SELECT a.id,b.name created_by_id,c.name latest_updated_by_idFROM student_data aLEFT JOIN creator_updater bON a.created_by = b.idLEFT JOIN creator_updater cON a.updated_by = c.id

• SQLFiddle 演示

有关加入的更多信息

• SQL 联接的可视化表示

let's say I have

table student_data and fields are

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

table creator_updater and fields are

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

so I wonder what is the exact code so it will show

student_data

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

i tried join syntax, but it seems different

解决方案

you need to join the creator_updater table twice so you can get the names of the two columns.

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

but the code above will not work if one of the columns is nullable, if that's the case, use LEFT JOIN instead of 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

For more information about joins

• Visual Representation of SQL Joins

这篇关于mysql连接2个表，2列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！