mysql - 过滤一对多关系查询结果的最佳方式。 MySQL

Question

使用多个连接来按多个属性值过滤查询结果是否是个好主意？例如:

product
--------
pid desc 
--------
1   prod1
2   prod2

attrib
--------
pid  aid val
--------
1    1   "avalue1"
1    2   "avalue2"

查询:

SELECT p.* FROM product p
INNER JOIN attrib a1 ON (p.pid=a1.pid)
INNER JOIN attrib a2 (p.pid=a2.pid)
WHERE a1.aid=1 and a1.val="avalue1" and a2.aid=2 and a2.val="avalue2"

或者有更好的方法吗？谢谢。

Answer 1

join 方法绝对有效，它可以利用索引。但是，我更喜欢使用 having 进行聚合，因为它更灵活:

select p.*
from product p join
     attrib a
     on p.pid = a.pid
group by p.pid
having sum(a.aid = 1 and a.val = 'avalue1') > 0 and
       sum(a.aid = 2 and a.val = 'avalue2') > 0;

having 子句中的每个条件都在计算满足其中一个条件的属性的数量。添加另一个条件很容易，无论是正面的还是负面的。例如，修改上面的代码以要求不存在 3/avalue3:

select p.*
from product p join
     attrib a
     on p.pid = a.pid
group by p.pid
having sum(a.aid = 1 and a.val = 'avalue1') > 0 and
       sum(a.aid = 2 and a.val = 'avalue2') > 0 and
       sum(a.aid = 3 and a.val = 'avalue3') = 0;