Oracle/SQL-查找具有一个不包含空值的值的记录 [英] Oracle/SQL - Finding records with one value excluding nulls

问题描述

我有一个非常类似于另一个问题的问题，但我不太清楚.这是原始问题的链接: Oracle /SQL-查找具有一个值的记录(类似记录除外)

I have a question very similar to another question but I can't quite figure it out. Here is the link to the original question:Oracle/SQL - Finding records with one value excluding by similar record

与该问题类似，我的记录将为1或为null.相同的记录可以是1或null的组合，在这些情况下，我想完全排除该记录.例如:

So similar to that problem, I have records that will either have a 1 or null. the same records can be a combination of 1 or null and in those instances, I want to exclude the record altogether. For example:

    Person  Type
    --------------
    Bob     1
    Sue     1
    Bob     null
    Tom     1
    Frank   1
    Frank   null
    Fred    null

我希望返回以下内容:

    Person  Type
    --------------
    Sue     1
    Tom     1

对此，任何方向都将不胜感激.我没有太多时间来解决这个问题，因此即使从概念上讲也会有所帮助！

Any direction on this would be very much appreciated. I dont have much time to solve this so even speaking conceptually will help!

我来的最近的是

    select person from table
    where type = 'S'
    MINUS
    select person from table
    where type is null

但是那当然行不通.

如果那是唯一的方法，我可以编写一个函数.谢谢！

I can write a function if that is the only way. Thank you!

推荐答案

尝试一下:

select person, type from table
where type = '1'
and person not in (select person from table where type is null)

这篇关于Oracle/SQL-查找具有一个不包含空值的值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！