MySQL查询跨多行查找匹配项 [英] MySQL query finding matches across multiple rows

问题描述

我有两个表，Data(Name, dataID)和Attributes(Name, attributeID, dataID)具有一对多关系.一个dataID可能与许多attributeID关联.

I have two tables, Data(Name, dataID) and Attributes(Name, attributeID, dataID) with a one-to-many relationship. One dataID might be associated with many attributeID's.

我想做的是运行一个查询，该查询查找具有一组特定的attributeID的所有dataID.我做不到:

What I want to do is run a query that finds all dataIDs that have a specific set of attributeIDs. I can't do:

SELECT dataID
FROM Attributes
WHERE dataID = 1 AND (attributeID = 1 OR attributeID = 2 OR attributeID = 3);

那将获取具有那些属性中任何一个的所有dataID，我希望具有所有这些属性的dataID.

That would grab all dataID's with any one of those attributes, I want the dataID's that have all of those attributes.

建议?

不仅使用非常基本的选择，还是让我全神贯注于查询.

Still wrapping my head around queries using more than very basic selects.

推荐答案

由于您需要读取Attributes表的三个不同行，因此我建议使用JOIN来避免子查询.

As you need to read three different rows of the Attributes table, I suggest to use JOIN's to avoid subqueries.

SELECT a1.dataID
FROM
    Attributes a1
    JOIN Attributes a2 ON
        a1.dataID=a2.dataID
    JOIN Attributes a3 ON
        a2.dataID=a3.dataID
WHERE
    a1.dataID = 1 AND
    a1.attributeID = 1 AND
    a2.attributeID = 2 AND
    a3.attributeID = 3;

这篇关于MySQL查询跨多行查找匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！