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

Question

我有一张 table ，上面有我给客户的备注。

这些笔记可以对所有管理用户公开，但用户只能拥有自己的私有(private)笔记。

我试过这个查询，但结果是 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,

Answer 1

你的意思是不是像这样:

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