mysql - SQL for left join 获取左表所有数据和右表条件数据

Question

table1               table2
|id  |  name |       |id  |userID | name  | duration | description |
+____+_______+       +____+_______+_______+__________+_____________+
|1   |  abc  |       |1   | 1     | abc   |   4      | abc         |
|2   |  pqr  |       |2   | 1     | pqr   |   2      | pqr         |
|3   |  mno  |       |3   | 1     | mno   |   7      | mno         |
|4   |  xyz  |       |4   | 2     | abc   |   5      | abc         |
|5   |  wxy  |       |5   | 2     | xyz   |   7      | xyz         |
+____________+       |6   | 3     | mno   |   8      | mno         |
                     |7   | 4     | pqr   |   4      | pqr         |
                     +____+_______+_______+__________+_____________+

result_table
|id  |userID | name  | duration | description |
+____+_______+_______+__________+_____________+
|1   | 2     | abc   |   5      | abc         |
|2   | null  | pqr   |   null   | null        |
|3   | null  | mno   |   null   | null        |
|4   | 2     | xyz   |   7      | xyz         |
|5   | null  | wxy   |   null   | null        |
+____+_______+_______+__________+_____________+

结果表必须如下所示。
table1 和 table2 之间的连接仅按 name 列执行，数据应按 userID 列过滤。

Answer 1

请尝试:

select

t1.id,
t2.userID,
t1.name,
t2.duration,
t2.description

from table1 t1

LEFT JOIN (SELECT id, userID, name, duration, description

FROM table2

WHERE userID = 2) t2 ON t1.name = t2.name