求SQL查询语句：为table1中product_id为空的物料填充对应值

表结构信息 #

Table1 #

material_num | product_id
-------------+-------------
ABCDE          NULL
THEJR          1ERT34
GTHUJ          23UJ45
HYUIJ          E9JLK5
DFOMC          NULL

Table2 #

item_number | material_number | id
------------+-----------------+-------
2003          DFOMC             3456
2005          ABCDE             5678
3456          GTHUJ             4857
4003          THEJR             8904
5678          HYUIJ             2002

需求说明 #

需要给Table1中product_id为空的material_num补全对应值，规则是：在Table2里，如果一个物料的id等于另一个物料的item_number，就把后者的product_id赋值给前者。比如ABCDE的id是5678，正好是HYUIJ的item_number，所以ABCDE的product_id就用HYUIJ的。

预期输出 #

material_number | product_id
----------------+-------------
ABCDE             E9JLK5
THEJR             1ERT34
GTHUJ             23UJ45
HYUIJ             E9JLK5
DFOMC             23UJ45

正确SQL查询语句 #

SELECT
    t1.material_num AS material_number,
    COALESCE(t1.product_id, t3.product_id) AS product_id
FROM
    table1 t1
LEFT JOIN table2 t2 ON t1.material_num = t2.material_number
LEFT JOIN table2 t2_link ON t2.id = t2_link.item_number
LEFT JOIN table1 t3 ON t2_link.material_number = t3.material_num
ORDER BY
    t1.material_num;

语句说明 #

• 先以Table1为基础表，关联Table2拿到每个物料对应的id；
• 再用这个id去匹配Table2里另一行的item_number，找到对应的关联物料；
• 接着通过关联物料匹配Table1，获取它的product_id；
• 最后用COALESCE函数，优先保留Table1原本非空的product_id，如果是空的就用推导出来的值。

内容的提问来源于stack exchange，提问作者sai priya