SQL查询——从表中选择不同的值

I have a table in which i have multiple entries against a FK. I want to find out the value of FK which do not have certain entries e.g
my table has following entries.

我有一个表格，其中有多个条目对一个FK。我想知道FK的值，它没有一定的元素e。我的表有以下条目。

PK----------------FK-----------------Column entries

1----------------100-----------------ab1
2----------------100-----------------ab2
3----------------100-----------------ab4
4----------------200-----------------ab1
5----------------200-----------------ab2
6----------------200-----------------ab3
7----------------300-----------------ab1
8----------------300-----------------ab2
9----------------300-----------------ab3
10---------------300-----------------ab4

Now, from this table i want to filter all those FK which do not have ab3 or ab4 in them. Certainly, i expect distinct values i.e. in this case result would be FK= 100 and 200.
The query which i am using is

现在，我想从这个表中过滤掉所有没有ab3或ab4的FK。当然，我希望有不同的值，即在这种情况下，结果是FK= 100和200。我正在使用的查询是

select distinct(FK) 
from table1 
where column_entries != 'ab3' 
   or column_entries != 'ab4';

Certainly, this query is not fetching the desired result.

当然，这个查询并没有获取所需的结果。

3 个解决方案

#1

try the following :-

试试以下:-

select distinct fk_col from table1
minus
(select distinct fk_col from table1 where col_entry='ab3'
intersect
select distinct fk_col from table1 where col_entry='ab4')

This will show all those FKs which do not have 'ab3' and 'ab4'. i.e. 100 and 200 in your case

这将显示所有没有“ab3”和“ab4”的FKs。也就是100和200

#2

The below script may be the solution if I got your question in a right way.

如果我正确地回答了你的问题，下面的脚本可能是解决方案。

SELECT DISTINCT(TableForeignKey)
FROM Test
WHERE TableForeignKey NOT IN (
SELECT T1.TableForeignKey
FROM Test T1 INNER JOIN Test T2 ON T1.TableForeignKey = T2.TableForeignKey
WHERE T1.TableEntry = 'ab3' AND T2.TableEntry = 'ab4')

SQLFiddle Demo

SQLFiddle演示

#3

You could use GROUP BY with conditional aggregation in HAVING:

你可以用有条件聚合的GROUP BY:

SELECT FK
FROM table1
GROUP BY FK
HAVING COUNT(CASE column_entries WHEN 'ab3' THEN 1 END) = 0
    OR COUNT(CASE column_entries WHEN 'ab4' THEN 1 END) = 0
;

The two conditional aggregates count 'ab3' and 'ab4' entries separately. If both end up with results greater than 0, then the corresponding FK has both 'ab3' and 'ab4' and is thus not returned. If at least one of the counts evaluates to 0, then FK is considered satisfying the requirements.

这两个条件聚合分别计数“ab3”和“ab4”项。如果两个结果都大于0，则对应的FK同时具有'ab3'和'ab4'，因此不会返回。如果至少一个计数计算为0，则FK被认为满足要求。

#1