mysql - 只选择喜欢或不喜欢的特定页面？

Question

我有 3 个表来存储人们的兴趣和喜好:pages、pages_likes、pages_dislikes

现在我想要一个主页来显示随机页面，但仅显示用户不喜欢或不喜欢的页面。

这是我的 SQL 语句:

SELECT p.id AS page_id, d.uid AS duid, l.uid AS luid, p.title FROM pages p 
JOIN pages_dislikes d on d.page_id = p.id JOIN pages_likes l on l.page_id = p.id 
WHERE d.uid != '3' AND l.uid != '3'  ORDER BY rand() LIMIT 1

但这仍然显示一个已被喜欢或不喜欢的页面，因为还有其他用户喜欢或不喜欢该页面，如下所示:

如何才能只显示特定用户 ID 尚未喜欢或不喜欢的页面？

Answer 1

您的查询应该是:

SELECT 
    p.id AS page_id
    ,d.uid AS duid
    ,l.uid AS luid
    ,p.title FROM pages p 
LEFT JOIN pages_dislikes d on d.page_id = p.id 
LEFT JOIN pages_likes l on l.page_id = p.id 
WHERE (d.page_id IS null OR l.page_id is null) AND d.uid != '3' AND l.uid != '3'
ORDER BY rand() LIMIT 1

您还可以尝试以下查询:

SELECT P.pageid, P.pagename FROM Pages P LEFT JOIN
(
    SELECT pageid FROM lik WHERE uid <> 3
    UNION 
    SELECT pageid FROM dis WHERE uid <> 3
) LikesDislikes ON LikesDislikes.pageid = P.pageid
ORDER BY rand() LIMIT 1