在一对多的情况下选择行

I think that I am stuck with this particular situation:

我认为我坚持这种特殊情况:

Here are my tables:

这是我的表格:

item_table:
id | item
1 : A
2 : B
3 : C

item_table:id |第1项:A 2:B 3:C

attr_table:
attr | item_id
1 : 1
1 : 2
2 : 1
2 : 3
3 : 2
3 : 3
I would like to know if it is technically possible to retrieve any item which is associated with attr = 1 and 3. The answer should be 'B' only. Likewise, if I request an item which is associated with attr = 1 and 2, I should get 'A' only.

attr_table:attr | item_id 1:1 1:2 2:1 2:3 3:2 3:3我想知道技术上是否可以检索与attr = 1和3相关的任何项目。答案应该是'B'只要。同样,如果我请求与attr = 1和2相关联的项目,我应该只获得'A'。

The thing is that attr_table can potentially have a lot of rows and I wish to make only one query.

问题是attr_table可能有很多行,我希望只进行一次查询。

This question sounds easy and I am quite upset for not being able to answer it.

这个问题听起来很简单,我对因无法回答而感到非常沮丧。

I was hoping that someone smarter could give me a hand...

我希望聪明的人可以帮我一把......

5 个解决方案

#1

The example is written for SQLServer but the query should work in mysql as wel.

该示例是为SQLServer编写的,但查询应该在mysql中工作。

Key is the HAVING COUNT statement being equal to the amount of attributes that have to match. If the attributes should be (1, 2, 5), you'll have to change the count to 3.

Key是HAVING COUNT语句等于必须匹配的属性数量。如果属性应为(1,2,5),则必须将计数更改为3。

DECLARE @item_table TABLE (ID INTEGER PRIMARY KEY, Item CHAR(1))
DECLARE @attr_table TABLE (Attr INTEGER, Item_ID INTEGER)

INSERT INTO @item_table VALUES (1, 'A')
INSERT INTO @item_table VALUES (2, 'B')
INSERT INTO @item_table VALUES (3, 'C')

INSERT INTO @attr_table VALUES (1, 1)
INSERT INTO @attr_table VALUES (1, 2)
INSERT INTO @attr_table VALUES (2, 1)
INSERT INTO @attr_table VALUES (2, 3)
INSERT INTO @attr_table VALUES (3, 2)
INSERT INTO @attr_table VALUES (3, 3)


SELECT Item
FROM @item_table i
     INNER JOIN @attr_table a ON a.Item_ID = i.ID
WHERE a.Attr IN (1, 3)
GROUP BY Item
HAVING COUNT(a.Attr) = 2

#2

   SELECT * From attr_table a, item_table i
   where a.item_id = i.id
   and a.attr = 1
   and a.item_id  in (select item_id from attr_table where  attr = 3);

Does the job returning one row for item B.

该作业是否为项目B返回一行。

#3

select * from item_table a 
where exists ( select * from attr_table b 
               where b.item_id = a.id and b.attr = 1)
and exists ( select * from attr_table c 
             where c.item_id = a.id and c.attr = 3);

Note that this query says exactly what your specification says: get me all the rows from item_table where there exists at least one row from attr_table that has that row's id and the first attr specified and where there exists at least one row from attr_table that has that row's id and the second attr specified.

请注意,此查询确切地说明了您的规范所说的内容:从item_table获取所有行,其中至少有一行来自attr_table,该行具有该行的id和指定的第一个attr,并且attr_table中至少存在一行具有该行行的id和指定的第二个attr。

#4

select distinct item_table.item from item_table, attr_table
where item_table.id = attr_table.item_id
and attr_table.attr = 1 and attr_table.attr = 3;

Basically it does the matching you'd expect and ends up with a ton of rows - but then the distinct keyword operates, so you get the minimal unique set of rows as your final result.

基本上它会进行您期望的匹配并最终得到大量的行 - 但是然后,distinct关键字会运行,因此您可以获得最小的唯一行集作为最终结果。

(Interally, I'd hope it's more efficient, but not bothering to make up the full list of matching rows).

(从某种程度上说,我希望它更有效率,但不会费心去构成匹配行的完整列表)。

#5

This is probably way too late, but I would suggest using a couple of joins like so:

这可能为时已晚,但我建议使用这样的几个连接:

select i.item, b.item_id, c.item_id 
from item_table i 
join attr_table b on i.id=b.item_id and b.item_id=1
join attr_table c on i.id=c.item_id and c.item_id=2

That's how I do it.

我就是这样做的。

#1