mysql - 如果两个现有列匹配，SQL 查询将添加新列并添加另一列的值

Question

表1列是(当前状态)

=======================================================
record_id |  ad_id | gender | gender_view | time_entry
=======================================================
1         |   1    |  male  |     45      | 1557072000
2         |   1    | female |     88      | 1557072000

表2列是(当前状态)

=====================
ad_id | submitted_by
=====================
1     |   1

匹配表 1 和表 2 的 ad_id 后，表 1 将拥有新列 subscribed_by。表 1 中的新列 Submitted_by 将采用表 2 中的 Submitted_by 值。

表1列(预期状态)

====================================================================
record_id |  ad_id | gender | gender_view | time_entry | submitted_by 
====================================================================
1         |   1    |  male  |     45      | 1557072000 |   1
2         |   1    | female |     88      | 1557072000 |   1

Answer 1

使用 ON T2.ad_id = T1.ad_id 的简单 LEFT JOIN 将返回您预期的结果:

SELECT T1.record_id,
       T1.ad_id,
       T1.gender,
       T1.gender_view,
       T1.time_entry
       COALESCE(T2.submitted_by, 0) submitted_by
FROM Table1 T1
LEFT JOIN Table2 T2 ON T2.ad_id = T1.ad_id