SQL:选择所有联接的记录都满足某些条件的记录 [英] SQL: Select records where ALL joined records satisfy some condition

问题描述

如何编写仅在联接表中的所有关联记录都满足某些条件的情况下才返回记录的SQL查询.

How can I write an SQL query that returns a record only if ALL of the associated records in a joined table satisfy some condition.

例如，如果A有很多B，我想从A SELECT * WHERE到给定A的所有相关B都具有B.some_val>值

For example, if A has many B, I want to SELECT * FROM A WHERE all related B's for a given A have B.some_val > value

我知道这可能是一个非常基本的问题，因此感谢您的帮助.另外，如果有所作为，我正在使用postgres.

I know this is probably a pretty basic question, so thanks for any help. Also, if it makes a difference, I'm using postgres.

山姆

推荐答案

假设不需要关联，请使用:

Assuming no need for correlation, use:

SELECT a.*
  FROM A a
 WHERE EXISTS(SELECT NULL
                FROM B b
              HAVING MIN(b.some_val) > a.val)

如果您确实需要关联:

SELECT a.*
  FROM A a
 WHERE EXISTS(SELECT NULL
                FROM B b
               WHERE b.id = a.id
              HAVING MIN(b.some_val) > a.val)

说明

EXISTS根据第一个匹配对布尔值求值-这使其比使用IN更快，并且-与使用JOIN不同-不会重复行. SELECT部分​​无关紧要-您可以将其更改为EXISTS SELECT 1/0 ...，尽管存在明显的零错误除法，但查询仍然可以工作.

Explanation

The EXISTS evaluates on a boolean, based on the first match - this makes it faster than say using IN, and -- unlike using a JOIN -- will not duplicate rows. The SELECT portion doesn't matter - you can change it to EXISTS SELECT 1/0 ... and the query will still work though there's an obvious division by zero error.

EXISTS中的子查询使用聚合函数MIN来获取最小的B.some_val-如果该值大于a.val值，则a.val小于所有b值. WHERE子句唯一需要的是关联-聚合函数只能在HAVING子句中使用.

The subquery within the EXISTS uses the aggregate function MIN to get the smallest B.some_val - if that value is larger than the a.val value, the a.val is smaller than all of the b values. The only need for a WHERE clause is for correlation - aggregate functions can only be used in the HAVING clause.

这篇关于SQL:选择所有联接的记录都满足某些条件的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！