MySQL查询多对多关系

本文介绍了MySQL查询多对多关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

一个非常简单的n:m关系示例，使我感到困惑.假设我们有两个表"Plant"和"Attribute"，并且它们之间还有另一个表，它们保持与ID的关系:

A very simple example of a n:m relationship that puzzles me. Let's assume we have two tables "Plant" and "Attribute" and another table between them holding their relationship with their IDs:

Plant--------hasAttribute--------Attribute P1 | A1 P1 | A2 P1 | A3 P2 | A1 P2 | A2 P3 | A2 P3 | A3

因此，植物1具有属性1,2和3.植物2具有属性1和2，植物3具有属性2和3. 现在，在一个查询中，如何获取例如所有具有属性2和3的植物? 结果应该返回P1和P3，因为它们都具有属性2和3. 我当时尝试工会，但结果也会给我P2 ...有什么想法吗?

So, Plant 1 has Attributes 1,2 and 3. Plant 2 has Attributes 1 and 2 and Plant 3 has Attributes 2 and 3. Now, in one single query, how can I get e.g. all the Plants that have Attribute 2 and 3? The result should return P1 and P3 because they both have Attributes 2 and 3. I was trying union but that will give me P2 as a result as well... any ideas?

推荐答案

此查询结构避免了使用唯一子句的情况(前提是解析表中没有重复的记录).

This query structure avoids the need for a distinct clause (provided there are no duplicate records in the resolution table).

SELECT p.PlantID FROM Plant p INNER JOIN PlantAttribute pa ON p.PlantID = pa.PlantID AND pa.AttributeID = 1 INNER JOIN PlantAttribute pa2 ON p.PlantID = pa2.PlantID AND pa2.AttributeID = 2;