MySQL查询与AND，OR和NOT [英] mysql query with AND, OR and NOT

问题描述

可以说我有文章的表与尽可能多的与主题一对多的关系。分配给文章每个主题有一个键入字段，它可以包含3个值的1 和，不是和或。

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)

我如何去创造这个查询？

How do I go about creating this query?

在此先感谢！

推荐答案

的全部实施，也不是件很简单的，你只要把它们连用与运算：

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

选择X的Y其中A和B和C不是D和E以及NOT即

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

和OR值之间走：

选择X的Y其中（（A和B以及C）和（D或E或F））AND NOT G和非H

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

替换小的数字与比较，就大功告成了。
所以，如果你想这样做在code，你的排序条件，然后只把它们连在一起作为一个字符串。小心避免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.

这篇关于MySQL查询与AND，OR和NOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！