SQL查找id-s,其中column的值等于特定行上的值

时间:2023-01-20 20:33:23

Let's say I work with the following table:

假设我使用下表:

ID   LETTER    VALUE

1       A      NON-VALID   
1       B      VALID 
1       C      VALID
1       D      VALID
2       A      NON-VALID
2       B      VALID
2       C      NON-VALID
2       D      VALID

So, what I want is simple, but I haven't found something similar or figured it out by myself.

所以,我想要的只是简单,但我没有找到类似的东西或自己想出来的东西。

I want to take the IDs that have let's say the value VALID in all the letters I will define.For example, if I want to have valid values on Bs and Cs, I want my data set to return ID 1.

我想在我将要定义的所有字母中使用可以说值VALID的ID。例如,如果我想在B和C上有有效值,我希望我的数据集返回ID 1。

Silly example:

愚蠢的例子:

SELECT ID 
FROM table 
WHERE VALUE=VALID IN EVERY ROW WHERE LETTER BETWEEN 'B' AND 'C'

1 个解决方案

#1


1  

You can do something like this:

你可以这样做:

select id
from t
where letter in ('B', 'C') and value = 'Valid'
group by id
having count(distinct letter) = 2;  -- "2" is the number of letters in your list

#1


1  

You can do something like this:

你可以这样做:

select id
from t
where letter in ('B', 'C') and value = 'Valid'
group by id
having count(distinct letter) = 2;  -- "2" is the number of letters in your list