带有 AND、OR 和 NOT 的 mysql 查询

2022-01-19 00:00:00 boolean boolean-logic mysql

假设我有一个文章表，其中包含与主题的多对多关系.分配给文章的每个主题都有一个 type 字段，该字段可以包含 AND、NOT 和 OR.

Lets say I have a table of articles with as many to many relationship with topics. Each topic assigned to an article has a type field which can contain 1 of 3 values AND, NOT, and OR.

Articles
   id
   ....

Topics
   id
   ....

ArticleTopics
   article_id
   topic_id
   type

我想创建一个查询返回所有具有以下内容的文章:

I want to create a query that says returns all articles that have:

ALL of the following topics: 1, 2, 3 (AND association)
   AND
ANY of the following topics: 4, 5, 6 (OR association)
   AND
NONE of the following topics 7, 8 (NOT association)

如何创建此查询?

提前致谢！

推荐答案

ALL 和 NOT 部分非常简单，只需用 AND 将它们链接起来:

The ALL and NOT parts are very simple, you just chain them with ANDs:

从 Y 中选择 X，其中 a AND b AND c AND NOT d AND e AND NOT e.

SELECT X FROM Y WHERE a AND b AND c AND NOT d AND e AND NOT e.

OR 介于:

从 Y 中选择 X WHERE ((a AND b AND c) AND (d OR e OR f)) AND NOT g AND NOT h

SELECT X FROM Y WHERE ((a AND b AND c) AND (d OR e OR f)) AND NOT g AND NOT h

用比较替换小数字，你就完成了.因此，如果您想在代码中执行此操作，请将您的条件排序，然后将它们作为字符串链接在一起.小心避免 SQL 插入.

replace small numbers with comparisons and you're done. So if you want to do this in code, sort your conditions and then just chain them together as a String. Be careful to avoid SQL-Insertions.

相关文章