在MySQL中设置交集:一种干净的方法 [英] Set intersection in MySQL: a clean way

问题描述

我想获取满足所有n个约束的实体.

I want to fetch entities which satisfy all n constraints I give them.

OR运算可以由UNION执行.我不会问这个问题，如果MySQL支持INTERSECT.

OR operations can be performed by UNION. I wouldnt have asked this question if MySQL supported INTERSECT.

我在表 subject 中有实体，并且它们的属性在* subject_attribute *中.

I have entities in the table subject and their attributes in *subject_attribute*.

我将AND操作视为嵌套查询的唯一方法:

I see the only way for AND operations as nested queries:

SELECT id
FROM subject_attribute
WHERE attribute = 'des_sen'
AND numerical_value >= 2.0
AND id
IN (
   SELECT id
   FROM subject_attribute
   WHERE attribute = 'tough'
   AND numerical_value >= 3.5
)

这意味着:获取满足最低子查询的实体，然后消除满足较高查询的实体"等等.

This means: " fetch entities which satisfy the lowest subquery, then eliminate those who satisfy a higher query" and so on.

rows(condn x) AND rows(condn y) AND rows(cond z) <--ideal
rows(condn x:rows(cond y:rows(cond z))) <-- I am stuck here

我更喜欢线性地链接条件，而不是像我想的那样嵌套它们

I prefer linearly chaining the conditions instead of nesting them as I want to

1. 以编程方式编写查询
2. 更好地调试它们

---

我的问题:给定 n个单独的查询，如何在MySQL中清晰，线性地对它们进行处理?

---

My question: Given n individual queries, how do I AND them cleanly and linearly in MySQL?

不使用嵌套查询或存储过程.

Not by using nested queries or stored procedures.

请注意有关单个查询的部分.

Please note the part about individual queries.

更新:乔纳森·莱夫勒(Jonathan Leffler)回答正确.马克·班尼斯特(Mark Ba​​nnister)的答案要简单得多(但我做出了一些错误的决定).如果您仍然对联接感到困惑，请参考我的答案.

Update: Jonathan Leffler answered it right. Mark Bannister's answer is much simpler (but I made some bad decisions). Please refer my answer if you are still confused on joins.

推荐答案

假定每个单独的查询都针对同一个表运行，并且每个查询都访问不同的attribute值，这是将所有此类查询相交的最简单方法查询的形式:

Assuming that each separate query is running against the same table, and that each of them is accessing different values of attribute, the simplest way of intersecting all such queries is of the form:

SELECT id
FROM subject_attribute
WHERE (attribute = 'des_sen' AND numerical_value >= 2.0) or
      (attribute = 'tough'   AND numerical_value >= 3.5) or
...
group by id
having count(distinct attribute) = N;

-其中N是属性数值值条件对的数量.

- where N is the number of attribute-numerical_value condition pairs.

这篇关于在MySQL中设置交集:一种干净的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！