mysql具有所有值 [英] mysql has all values

问题描述

这与我的最后一个问题有关

This is relating to my last question mysql query with AND, OR and NOT

我不是在编辑问题，而是在问一个新问题，因为该问题只是前一个问题的一部分而已更改.

Instead of editing the question, I am asking a new one because the question is only part of the previous question with an alteration.

我正在寻找一个mysql查询，该查询将向我返回所有具有所有必需主题的文章.

I am looking to do a mysql query that returns me all articles that have all required topics.

Article
   id
   ....

Topic
   id
   ....

ArticleTopics
   article_id
   topic_id
   type

可以有效执行的操作:

SELECT * FROM Article LEFT JOIN ArticleTopics ON Article.id = ArticleTopics.article_id
WHERE ArticleTopics.topic_id HAS ALL (these topics)

这可能吗?最好的方法是什么?

Is this possible? What is the best approach for this?

推荐答案

其他几个答案建议在子表上为每个filter子句使用别名-这可能效率不高或扩展性不佳.

Several of the other answers suggest using aliases on the child table for each filter clause - this may not be very efficient or scale well.

考虑:

SELECT x.*
FROM Article x INNER JOIN
(SELECT t.article_id, COUNT(t.article_id)
  FROM articleTopics t
  WHERE t.topic_id IN ([your_list_of_topics])
  GROUP BY t.article_id
  HAVING COUNT(t.article_id)>=[number of elements in [your_list_of_topics]]
  ORDER BY COUNT(t.article_id) DESC
  LIMIT 0,100) AS ilv
ON x.id=ilv.article_id

此方法的另一个优点是查询的结构不需要随要搜索的主题数而改变-您甚至可以将它们放在临时表中并执行联接，而不必使用'IN (...)'字面意思.

Another advantage of this approach is that the structure of the query doesn't need to change with the number of topics you are searching for - you could even put them in a temporary table and perform a join instead of using the ' IN (...)' literal.

您需要尝试一下以查看哪个查询的效果更好.

You'd need to try it out to see which query behaves better.

这篇关于mysql具有所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！