Oracle/SQL-查找具有一个值的记录，但类似记录除外 [英] Oracle/SQL - Finding records with one value excluding by similar record

问题描述

好的，让我更好地解释一下.假设我有一个人员专栏和一个类型专栏.同一个人可能多次出现在表中，但类型不同.我希望所有具有指定类型的人，除非他们与其他类型一起列出.

Okay let me do a better job explaining this. Assume I have a person column and a type column. The same person could be in the table multiple times, but with different types. I want all the people who have a specified type UNLESS they are listed with other types.

所以给定这些数据

Person  Type
--------------
Bob     S
Sue     S
Bob     O
Tom     S
Frank   S
Frank   R

我想查看具有S型但又没有列出O型或R型的人员.因此我的查询应该返回

I want to see Persons who have the Type S, but are also not listed with either Type O or R. So my query should return

Person  Type
--------------
Sue     S
Tom     S

谢谢！

推荐答案

这可以做到:

SELECT person
FROM table
GROUP BY person
HAVING MIN(type) = 'S' AND MAX(type) = 'S'

但是，如果您有同一个人的多个记录并键入"S"，则将删除这些重复项

However if you have multiple records for the same person and type 'S', then this will remove those duplicates

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