mysql - 如何从同一张表中正确编写JOIN的SQL查询？

我有一张桌子，上面有给客户的笔记。

这些便笺可以对所有管理用户公开，但是该用户只能拥有自己的私人便笺。

我尝试了此查询，但结果是0行。

select c0.*
from customer_note as c0
       left join customer_note as c1 on c0.id = c1.id
where c0.private = 0
and (c1.private = 1 and c1.user_id = 2)

该查询完全满足我的需要，但我想避免使用UNION子句。

select c0.*
from customer_note as c0
       left join customer_note as c1 on c0.id = c1.id
where c0.private = 0

union

select c1.*
from customer_note as c1
where c1.private = 1
  and c1.user_id = 2

表结构：

`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`note` longtext COLLATE utf8_unicode_ci NOT NULL,
`create_stamp` datetime NOT NULL,
`private` tinyint(1) NOT NULL,
`state` int(11) NOT NULL,

最佳答案

您不是简单地说：

select c0.*
from   customer_note as c0
where  c0.private = 0
    or (c0.private = 1 and c0.user_id = 2)

关于mysql - 如何从同一张表中正确编写JOIN的SQL查询？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57394635/