主页 / dba / 问题 / 28431
Accepted
Jmoney38
Asked: 2012-11-10 06:46:11 +0800 CST

单个查询中的多对一子选择

  • 772

我有两个表，外键来自 T1->T2，是一对多的关系。也就是说，表 T1 中的 1 个元组与 T2 中的 0..N 个元组相关联。

举一个简单的例子，假设 T1 是汽车，T2 是一个缺陷表。因此，一辆汽车可以有 0..N 个缺陷，我们将这些缺陷作为整数存储在 T2 中。

我想仅从 Cars 中包含缺陷 i1和i2 的那些汽车中选择 *。

相反，执行 OR 非常简单：

SELECT * FROM cars AS T1 
  WHERE EXISTS (
    SELECT imperfection FROM Imperfections as T2 
      WHERE T1.uid = T2.uid AND (imperfection = 1 OR imperfection = 2)
  );

我一直在尝试使用交集来设置逻辑，但在这一点上，我想知道我是否过于复杂了。

database-design postgresql

3 个回答

  1. Best Answer

    您可以有两个单独的存在子句：

    SELECT * 
FROM cars AS T1 
WHERE 
    EXISTS 
    (
        SELECT * FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection = 1
    )
    AND EXISTS
    (
        SELECT * FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection = 2
    );

    这意味着您的 Imperfects 表有两个左半连接，但是如果您有 UID 和 Imperfect 索引，那么这些应该是微不足道的。

    在这种情况下，使用 WHERE EXISTS 优于 JOIN 有两个优点。第一个是左半连接在第一次匹配后停止，因此它可以比连接更快（需要遍历所有记录）。第二个是，如果您不知何故没有良好的数据约束，并且在 Imperfects 中有两条具有相同 UID 和 Imperfect 的记录，您最终可能会在主查询中返回重复的行。

    • 7

  2. 最简单最短的方法是

    SELECT c.* 
FROM 
    cars AS c
    JOIN Imperfections as i1 ON c.uid = i1.uid AND i1.imperfection = 1
    JOIN Imperfections as i2 ON c.uid = i2.uid AND i2.imperfection = 2
;

    对自身存在的(INNER) JOINs考验。

    这成立 if (uid, imperfection)is UNIQUE，这在此处是合理的，但尚未定义。可能有缺陷的子类型......那么你需要一个DISTINCTor GROUP BY。USING (uid)甚至会比ON c.uid = i1.uid. 缺陷将进入一个WHERE条款。

    下一个想法与凯文在他的回答中描述的相同，所以我不会在这里包含它。

    • 5 
  3. SELECT * 
FROM cars AS T1 
WHERE 
    EXISTS 
    (
        SELECT 1 FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection IN(1,2)
        HAVING COUNT(DISTINCT imperfection) =2
     )
    • 3

相关问题