寻找匹配两个关键字的帖子(两者都不是,而不仅仅是其中一个).
我猜以下MySQL查询返回不匹配的原因是匹配“climate”的关键字和匹配“recycle”的关键字与t_keywords表中的行不同.你会怎么做?
SELECT t_posts.id, t_posts.title
FROM t_posts, t_keywords, t_posts_keywords
WHERE t_posts.id = t_posts_keywords.id_post
AND t_keywords.id = t_posts_keywords.id_keyword
AND t_keywords.keyword = "climate"
AND t_keywords.keyword = "recycling"
GROUP BY t_posts.id
解决方法:
SELECT t_posts.id, t_posts.title
FROM t_posts, t_keywords, t_posts_keywords
WHERE t_posts.id = t_posts_keywords.id_post
AND t_keywords.id = t_posts_keywords.id_keyword
AND t_keywords.keyword IN ('climate', 'recycling')
GROUP BY t_posts.id HAVING count(t_keywords.id) = 2
您已经按帖子进行分组,所以您只需要检查给定的帖子在原始数据中有两行,每个关键字都有一行 – 这就是HAVING的用途.如果要匹配的关键字数量可变,那么“2”也必须是变量,替换为应用程序的正确计数.
标签:mysql,select
来源: https://codeday.me/bug/20190623/1273892.html