mysql - 如何将有时金额为空的字段连接到表

Question

Pasteid字段是一个有时为空的字段，满时引用post表，post表通过Userid字段连接到user表.

select `posts`.`id` as `pid`, `posts`.`media` as `pmedia`, `posts`.`created_at` as `pcreated_at`, `posts`.`txt`, `users`.`media` as `umedia`, `users`.`id`, `users`.`name` as `name`, `users`.`username`, `posts`.`options`, `posts`.`likes`, `posts`.`comments`, `posts`.`copyid`, `posts`.`pasteid`, `u`.`name` as `replayname`, `u`.`name` as `replayuser` 
from `posts` 
inner join `usersq` on `users`.`id` = `posts`.`userid` 
inner join `posts` as `p` on `posts`.`pasteid` = `p`.`id` 
inner join `users` as `u` on `u`.`id` = `p`.`userid` 
where (`posts`.`block` = 0 and `users`.`view` = 1) and `users`.`status` not in (1, 3) 
order by `posts`.`created_at` desc 
limit 30

事实上，我们想知道该帖子是否被复制，以及它是从哪个副本复制的。上面代码的问题在于，它只包含 Pasteid 字段必须填写的记录，如果为空，则不包含输出

帖子表id txt 媒体粘贴id 用户id1 eee 1.jpg 12 RR 13 eee 1.jpg 1 2

用户表身份证号码 1 阿里 2 插孔

如您所见，插孔已从后面复制。现在我需要从 users 表中获取 Ali 的名字并将其放入 replayname 字段

Answer 1

只需使用LEFT JOIN而不是INNER JOIN。LEFT JOIN 将返回左表中的所有记录，即使它们在右表中没有匹配项。

select `posts`.`id` as `pid`, `posts`.`media` as `pmedia`, `posts`.`created_at` as `pcreated_at`, `posts`.`txt`, `users`.`media` as `umedia`, `users`.`id`, `users`.`name` as `name`, `users`.`username`, `posts`.`options`, `posts`.`likes`, `posts`.`comments`, `posts`.`copyid`, `posts`.`pasteid`, `u`.`name` as `replayname`, `u`.`name` as `replayuser` 
from `posts` 
inner join `usersq` on `users`.`id` = `posts`.`userid` 
left join `posts` as `p` on `posts`.`pasteid` = `p`.`id` 
left join `users` as `u` on `u`.`id` = `p`.`userid` 
where (`posts`.`block` = 0 and `users`.`view` = 1) and `users`.`status` not in (1, 3) 
order by `posts`.`created_at` desc 
limit 30