问题描述
假设我有一个文章表，其中包含与主题的多对多关系.分配给文章的每个主题都有一个 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.
这篇关于带有 AND、OR 和 NOT 的 mysql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！