主页 / dba / 问题 / 133271
Accepted
user3202098
Asked: 2016-03-25 03:59:49 +0800 CST

SQL从一个表中获取另一个表中的多个条目的ID

  • 772

在我的数据库中，我有两个表：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;
performance query-performance

1 个回答

  1. Best Answer

    显然，您的PersonAttributes表是使用EAV模型设计的。该模型具有易于扩展的优点：属性存储为行，添加新行很容易。但是，查询这种表比那些设计的传统方式（属性存储为列）更困难。

    您的解决方案很好地说明了使用 EAV 建模表完成一项相当简单的任务可能会带来多少麻烦。它实际上是解决像您这样的问题的常用方法之一，尽管我建议您尝试在不使用派生表的情况下重写它 - 如下所示：

    SELECT p.ID 
FROM Persons AS p
  JOIN PersonAttributes AS paB ON p.ID = paB.ID
  JOIN PersonAttributes AS paC ON p.ID = paC.ID
  JOIN PersonAttributes AS paD ON p.ID = paD.ID
WHERE paB.Attr = 'b'
  AND paC.Attr = 'c'
  AND paD.Attr = 'd';

    性能可能与您的语法保持一致，但在不使查询更快的情况下，这种重写至少会使其更简洁，并且可以说更具可读性。

    话虽如此，您还可以使用另一种相当常见的方法，随着属性数量的增加，它可能会提供更好的性能。它使用分组和聚合：

    SELECT
  ID
FROM
  PersonAttributes
WHERE
  Attr IN ('b', 'c', 'd')
GROUP BY
  ID
HAVING
  COUNT(*) = 3
;

    通过这种方法，具有任何指定属性的所有行都被检索并按ID. 为了确定具有所有三个属性的组（人），引入了一个 HAVING 过滤器来比较每个组中的行数*与列表中的属性总数IN。

    如果您可以负担将要搜索的属性存储在（临时）表中，则该方法可以稍微通用化。这是在这种情况下的样子：

    SELECT
  pa.ID
FROM
  PersonAttributes AS pa
  INNER JOIN QueriedAttributes AS qa ON pa.Attr = qa.Attr
GROUP BY
  pa.ID
HAVING
  COUNT(*) = (SELECT COUNT(*) FROM QueriedAttributes)
;

    这里没有 WHERE 子句——它被查询属性表的连接所取代，匹配所需的属性总数来自同一个表，而不是硬编码。

    这种问题通常被称为关系划分。Joe Celko 在这篇文章中对此进行了详细讨论：

    *分组方法的这种特殊实现假设每个人的每个属性总是有一行，因此COUNT(*)可以正常工作。如果相同类型的属性可能或以后允许每人重复，请COUNT(DISTINCT Attr)改为使用。

    • 3

相关问题