排除基于其他行的行(SQL) [英] Exclude rows based on other rows (SQL)

问题描述

说我有一个这样的查询:

Say I have a query like this:

SELECT *
FROM TABLE

它返回此:

TABLE
ID | DATA | VAL
===============
01 | ABCD | 1
01 | DEFG | 2
02 | FGHI | 3
02 | HIJK | 2
03 | JKLM | 3
03 | LMNO | 4
04 | NOPQ | 0
04 | PQRS | 1

当前，我有一个查询尝试仅查找类似这样的好值，但是它存在缺陷，因为包括了其他行中VAL不好的ID，这不是我想要的.

Currently I have a query that attempts find only good values like this, but it's flawed because IDs that have bad VALs in other rows are included, which is not what I'd like.

SELECT *
FROM TABLE
WHERE TABLE.VAL IN ("1","2","3")

将返回此消息(缺少LMNO和PQRS):

would return this (with LMNO and PQRS missing):

TABLE
ID | DATA | VAL
===============
01 | ABCD | 1
01 | DEFG | 2
02 | FGHI | 3
02 | HIJK | 2
03 | JKLM | 3
04 | NOPQ | 0

但是，我只希望ID没有错误值的行.因此，01和02很好，因为它们的所有行都具有良好的结果. 03和04不好，因为它们被其他行的不好结果所污染.

However, I only want rows where the ID has NO bad values. So, 01 and 02 are fine because all of their rows have good results. 03 and 04 are bad because they're tainted by the bad results in other rows.

我可以这样输入结果，然后在软件中进行处理，但是似乎可以在数据库中做到这一点，并且通常来说，在数据库上完成比在软件中做得更好(你知道，这就是他们在那里的目的...)

I could just bring the result in like this and process it that way in software, but it seems as though this should be possible with a database, and as a general rule, doing it on the database is better than in software (you know, that's kind of what they're there for...)

我能想到的最好的方法是:

The best I could come up with is this:

SELECT *
FROM TABLE
WHERE COUNT( SELECT ID
             FROM TABLE
             WHERE TABLE.VAL NOT IN ("1","2","3")
           ) = 0

这可行吗?有更好的选择吗?

Is this viable? Is there a better alternative?

谢谢！

推荐答案

使用:

SELECT * 
  FROM TABLE a
 WHERE a.val IN (1,2,3)
   AND NOT EXISTS(SELECT NULL
                    FROM TABLE b
                   WHERE b.id = a.id
                     AND b.val NOT IN (1, 2, 3))

这篇关于排除基于其他行的行(SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！