在我的数据库中,我有两个表:Persons 和 PersonAttributes。
一个人可能有几个与之关联的属性。
我想做的是在 PersonAttributes 表中获取至少具有与其关联的指定属性的所有人员(/ ID)的列表。
对于一个属性,只需一个 WHERE 语句即可轻松完成。我的问题是我想为多个属性执行此操作。
我能想出的唯一解决方案是为每个属性做一个 SELECT ,然后加入它们。虽然我可以以编程方式构建这样的查询,但它看起来非常复杂,我希望可能有一个更简单的解决方案。
为了更好地理解这里是一个SQLFiddle,包括我对 3 个属性的解决方案。
编辑:更改了 SQLFiddle 链接。查询现在看起来像这样:
SELECT Persons.ID
FROM Persons
JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'b') t1
ON Persons.ID = t1.ID
JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'c') t2
ON t1.ID = t2.ID
JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'd') t3
ON t2.ID = t3.ID;
显然,您的
PersonAttributes
表是使用EAV模型设计的。该模型具有易于扩展的优点:属性存储为行,添加新行很容易。但是,查询这种表比那些设计的传统方式(属性存储为列)更困难。您的解决方案很好地说明了使用 EAV 建模表完成一项相当简单的任务可能会带来多少麻烦。它实际上是解决像您这样的问题的常用方法之一,尽管我建议您尝试在不使用派生表的情况下重写它 - 如下所示:
性能可能与您的语法保持一致,但在不使查询更快的情况下,这种重写至少会使其更简洁,并且可以说更具可读性。
话虽如此,您还可以使用另一种相当常见的方法,随着属性数量的增加,它可能会提供更好的性能。它使用分组和聚合:
通过这种方法,具有任何指定属性的所有行都被检索并按
ID
. 为了确定具有所有三个属性的组(人),引入了一个 HAVING 过滤器来比较每个组中的行数*与列表中的属性总数IN
。如果您可以负担将要搜索的属性存储在(临时)表中,则该方法可以稍微通用化。这是在这种情况下的样子:
这里没有 WHERE 子句——它被查询属性表的连接所取代,匹配所需的属性总数来自同一个表,而不是硬编码。
这种问题通常被称为关系划分。Joe Celko 在这篇文章中对此进行了详细讨论:
*分组方法的这种特殊实现假设每个人的每个属性总是有一行,因此
COUNT(*)
可以正常工作。如果相同类型的属性可能或以后允许每人重复,请COUNT(DISTINCT Attr)
改为使用。