mysql的leftjoin一对多关系
A left join B
1对0
左表A对应右表B为NULL
1对多
右表B有多少条，结果就会有多少条
原始数据
| id | t1 | t2 | t3 | t4 | created_at |
| 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | ||
| 4 | 1 | 22 | 2021-06-20 21:00:06.557704 | ||
| 5 | 1 | 33 | 2021-06-20 21:00:19.692238 | ||
| 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | ||
| 7 | 2 | 22 | 2021-06-20 21:00:30.355809 | ||
| 8 | 2 | 33 | 2021-06-20 21:00:35.397191 |
SQL
select * from test a LEFT JOIN test b on a.t1=b.t1
结果
| id | t1 | t2 | t3 | t4 | created_at | id(1) | t1(1) | t2(1) | t3(1) | t4(1) | created_at(1) |
| 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | ||||
| 4 | 1 | 22 | 2021-06-20 21:00:06.557704 | 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | ||||
| 5 | 1 | 33 | 2021-06-20 21:00:19.692238 | 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | ||||
| 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | 4 | 1 | 22 | 2021-06-20 21:00:06.557704 | ||||
| 4 | 1 | 22 | 2021-06-20 21:00:06.557704 | 4 | 1 | 22 | 2021-06-20 21:00:06.557704 | ||||
| 5 | 1 | 33 | 2021-06-20 21:00:19.692238 | 4 | 1 | 22 | 2021-06-20 21:00:06.557704 | ||||
| 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | 5 | 1 | 33 | 2021-06-20 21:00:19.692238 | ||||
| 4 | 1 | 22 | 2021-06-20 21:00:06.557704 | 5 | 1 | 33 | 2021-06-20 21:00:19.692238 | ||||
| 5 | 1 | 33 | 2021-06-20 21:00:19.692238 | 5 | 1 | 33 | 2021-06-20 21:00:19.692238 | ||||
| 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | ||||
| 7 | 2 | 22 | 2021-06-20 21:00:30.355809 | 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | ||||
| 8 | 2 | 33 | 2021-06-20 21:00:35.397191 | 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | ||||
| 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | 7 | 2 | 22 | 2021-06-20 21:00:30.355809 | ||||
| 7 | 2 | 22 | 2021-06-20 21:00:30.355809 | 7 | 2 | 22 | 2021-06-20 21:00:30.355809 | ||||
| 8 | 2 | 33 | 2021-06-20 21:00:35.397191 | 7 | 2 | 22 | 2021-06-20 21:00:30.355809 | ||||
| 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | 8 | 2 | 33 | 2021-06-20 21:00:35.397191 | ||||
| 7 | 2 | 22 | 2021-06-20 21:00:30.355809 | 8 | 2 | 33 | 2021-06-20 21:00:35.397191 | ||||
| 8 | 2 | 33 | 2021-06-20 21:00:35.397191 | 8 | 2 | 33 | 2021-06-20 21:00:35.397191 |
优化
select * from test a LEFT JOIN (select * from test group by t1) b on a.t1=b.t1
| id | t1 | t2 | t3 | t4 | created_at | id(1) | t1(1) | t2(1) | t3(1) | t4(1) | created_at(1) |
| 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | ||||
| 4 | 1 | 22 | 2021-06-20 21:00:06.557704 | 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | ||||
| 5 | 1 | 33 | 2021-06-20 21:00:19.692238 | 3 | 1 | 11 | 2021-06-20 21:00:01.540233 | ||||
| 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | ||||
| 7 | 2 | 22 | 2021-06-20 21:00:30.355809 | 6 | 2 | 11 | 2021-06-20 21:00:24.792786 | ||||
| 8 | 2 | 33 | 2021-06-20 21:00:35.397191 | 6 | 2 | 11 | 2021-06-20 21:00:24.792786 |