在SQL的另一列中提取具有指定公共值的元组 [英] Extract tuples with specified common values in another column in SQL

问题描述

我有一个类似的数据集:

I have a dataset that look like:

 Col1    Col2    
 1        ABC 
 2        DEF
 3        ABC 
 1        DEF 

预期输出:

Col1     Col2    
 1        ABC 
 1        DEF

我只想从Col1中提取在列中同时具有值​​ABC和DEF的那些IDS.

I want to extract only those IDSs from Col1 which have both values ABC and DEF in the column.

我在SQL中尝试了self-join，但这没有给我预期的结果.

I tried the self-join in SQL but that did not give me the expected result.

SELECT DISTINCT Col1
FROM db A, db B
WHERE A.ID <> B.ID
    AND A.Col2 = 'ABC'
    AND B.Col2 = 'DEF' 
GROUP BY A.Col1

此外，我尝试使用以下代码在R中进行同样的操作:

Also, I tried to the same thing in R using the following code:

vc <- c("ABC", "DEF")
data1 <- db[db$Col2 %in% vc,]

同样，我没有得到想要的输出.谢谢您提前完成所有指示.

Again, I did not get the desired output. Thanks for all the pointers in advance.

推荐答案

以下是您当前的查询已更正:

Here is your current query corrected:

SELECT DISTINCT t1.Col1
FROM yourTable t1
INNER JOIN yourTable t2
    ON t1.Col1 = t2.Col1
WHERE t1.Col2 = 'ABC' AND t2.Col2 = 'DEF';

演示

联接条件是两个Col1值都是相同，第一个Col2值是ABC，第二个Col2值是DEF.

The join condition is that both Col1 values are the same, the first Col2 value is ABC and the second Col2 value is DEF.

但是，我可能会使用以下规范方法:

But, I would probably use the following canonical approach to this:

SELECT Col1
FROM yourTable
WHERE Col2 IN ('ABC', 'DEF')
GROUP BY Col1
HAVING MIN(Col2) <> MAX(Col2);

这篇关于在SQL的另一列中提取具有指定公共值的元组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！