我有3张桌子。
myMembers
------------------------------------
id | username | privacy
------------------------------------
1 | userA | 0
2 | userB | 1
3 | userC | 0
4 | userD | 1
following
--------------------------------
id | user_id | follower_id
--------------------------------
1 | 2 | 1
posts
-------------------------------------
id | userID | username | statusMsg
--------------------------------------
1 | 4 | userD | Issac Newton is genius
2 | 2 | userB | Newton Saw apple
3 | 3 | userC | Newtonian Physics
4 | 1 | userA | Calculus came from Sir Newton
有一个搜索字段。当登录用户在表"帖子"中搜索"关键字"时，我想省略那些将隐私设置为"1"并且 WHERE 搜索者没有关注用户 B 的用户的结果。
查询在逻辑上应该这样做。
SELECT * from posts WHERE (match the keyword)
AND (
if (poster's privacy (which is set in myMembers)==1){
if (seacher is following poster){
select this post
}
}
else { select this post
}
)
LIMIT results to 5 rows
所以对于关键词"牛顿"，如果用户 A 正在搜索，则应返回"帖子"中的第 2、3、4 行。如果用户 D 正在搜索，则只应返回"帖子"中的第 1、3 和 4 行，
基于隐私和关注编辑：标记以供将来搜索：mySql 中 WHERE 子句中的 IF 条件
请尝试以下查询（也在SQL Fiddle上）：
SELECT p.id, p.user_id, m.username, m.privacy,
searcher.username "Searcher", p.status_msg
FROM posts p
JOIN members m ON m.id = p.user_id
LEFT JOIN following f ON p.user_id = f.user_id
JOIN members searcher ON searcher.username = 'userA'
WHERE (m.privacy = 0 OR (m.privacy = 1 AND f.follower_id = searcher.id)
OR m.id = searcher.id)
AND p.status_msg LIKE '%New%'
ORDER BY p.id
LIMIT 5;
我从表中删除username字段posts因为它是多余的。此外，我对表和列的命名略有不同，因此查询可能需要对您的架构进行外观更改。
WHERE子句中的第一行是您要查找的行，它按以下顺序选择帖子：
- 来自没有隐私的成员的第一个帖子;
- 然后是成员的帖子，后跟当前
searcher; - 最后，成员本人的帖子。
编辑：
此查询使用原始标识符：
SELECT p.id, p.`userID`, m.username, m.privacy,
searcher.username "Searcher", p.`statusMsg`
FROM posts p
JOIN `myMembers` m ON m.id = p.`userID`
LEFT JOIN following f ON p.`userID` = f.user_id
JOIN `myMembers` searcher ON searcher.username = 'userD'
WHERE (m.privacy = 0 OR f.follower_id = searcher.id OR m.id = searcher.id)
AND p.`statusMsg` LIKE '%New%'
ORDER BY p.id
LIMIT 5;
编辑2：
为了避免在posts表中有多个用户关注者的情况下出现重复，应按以下方式更改联接和筛选条件（在 SQL Fiddle 上）：
SELECT p.id, p.user_id, m.username, m.privacy,
searcher.username "Searcher", p.status_msg
FROM posts p
JOIN members m ON m.id = p.user_id
JOIN members searcher ON searcher.username = 'userC'
LEFT JOIN following f ON p.user_id = f.user_id
AND follower_id = searcher.id
WHERE (m.privacy = 0 OR (m.privacy = 1 AND f.id IS NOT NULL)
OR m.id = searcher.id)
ORDER BY p.id
LIMIT 5;
尝试以下操作：
SET @my_user_id= 1;
SELECT * FROM posts p
INNER JOIN myMembers m ON p.user_id= m.id
WHERE statusMsg LIKE '%'
AND privacy=0
AND user_id IN (SELECT follower_id FROM following f WHERE f.user_id=@my_user_id)
LIMIT 5
试试这个：
SELECT a.*
FROM posts a
LEFT JOIN (SELECT user_id
FROM following a1
INNER JOIN myMembers b1
ON a1.follower_id = b1.id
WHERE a1.follower_id = 1 AND
b1.privacy = 1
) b
ON a.userID = b.user_id AND
WHERE a.statusMsg LIKE '%search%' AND
b.user_id IS NULL
LIMIT 5;
或没有子查询的更好方法：
SELECT a.*
FROM posts a
LEFT JOIN myMembers b
ON a.userID = b.id AND
b.privacy = 1
LEFT JOIN following c
ON a.userID = c.user_id AND
c.follower_id = 1
WHERE a.statusMsg LIKE '%search%' AND
b.id IS NULL AND
c.user_id IS NULL
LIMIT 5;
请参见：SQL 联接的直观说明