分享
三行代码  ›  专栏  ›  技术社区  ›  Lucca Zenobio

联接表和查找差异

  •  1
  • Lucca Zenobio  · 技术社区  · 5 天前

    Table1
        +------------------+--------------------+-------------------+-------------+-------------+
        |student_id|project_id|name|project_name|approved|evaluation_type|grade| cohort_number|
    

    我还有一张桌子,上面有:

    Table2
        +-------------+----------+
        |cohort_number|project_id|
    

    我的问题是:我想为每一个 学生证 队列数 . 基本上我需要两个表之间的“差异”。我想通过与表2的比较,用缺少的条目填充表1 项目id 对于那个队列的数字。

    左连接

    Table1
    
        |student_id|project_id|name|           project_name| approved|evaluation_type|             grade|cohort_number|
        +----------+----------+--------------------+------+--------------------+--------+---------------+------------------
        |        13|        18|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
        |        13|         7|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
        |        13|        27|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
    

    +-------------+----------+
    |cohort_number|project_id|
    +-------------+----------+
    |            3|        18|
    |            3|        27|
    |            4|        15|
    |            3|         7|
    |            3|        35|
    

    我想要:

        |student_id|project_id|name|           project_name| approved|evaluation_type|             grade|cohort_number|
        +----------+----------+--------------------+------+--------------------+--------+---------------+------------------
        |        13|        18|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
        |        13|         7|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
        |        13|        27|Name|  project/sd-03-bloc...|    true|       standard|               1.0|            3|
        |        13|        35|Name|  project/sd-03-bloc...|    false|       standard|                 0|            3|
    

    提前谢谢

    1 回复  |  直到 5 天前
        1
  •  1
  •   GMB    5 天前

    如果我没听错的话,你会发现 (student_id, cohort_number, name) table1 ,然后从 table2 . 这基本上给了你一行的每个项目,一个学生 完整的。

    然后你可以带 表1 left join . "“缺失”项目由 null project_name , approved , evaluation_type , grade .

    select 
        s.student_id,
        t2.project_id,
        s.name,
        t1.project_name,
        t1.approved,
        t1.evaluation_type,
        t1.grade,
        s.cohort_number
    from (select distinct student_id, cohort_number, name from table1) s
    inner join table2 t2 
        on  t2.cohort_number = s.cohort_number
    left join table1 t1
        on  t1.student_id = s.student_id 
        and t1.project_id = t.project_id