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.
So given this data
Person Type
--------------
Bob S
Sue S
Bo开发者_运维技巧b O
Tom S
Frank S
Frank 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
Thanks!
This will do:
SELECT person
FROM table
GROUP BY person
HAVING MIN(type) = 'S' AND MAX(type) = 'S'
However if you have multiple records for the same person and type 'S', then this will remove those duplicates
select person, type
from myTable
where type = 'S'
and person NOT IN (
select person
from myTable
where type in ('O', 'R')
)
SELECT DISTINCT person, ptype FROM persons p1
WHERE (SELECT COUNT( DISTINCT ptype )
FROM persons p2
WHERE p2.person = p1.person) = 1;
How about this:
SELECT person, type
FROM MyTable t1
WHERE (
SELECT COUNT( DISTINCT type )
FROM @MyTable t2
WHERE t2.person = t1.person
AND t1.Type='s'
) = 1
GROUP BY person, type
As an option to the NOT IN syntax, an outer join could handle that too
select t1.person, t1.type
from person_Type t1, person_type t2
where t1.person = t2.person(+)
and t1.type != t2.type(+)
and t1.type = 'S'
and t2.type is null;
Or, in response to the comment - for those who prefer the ANSI syntax...
select t1.person, t1.type
from person_type t1
left outer join person_type t2
on t2.person = t1.person
and t2.type != t1.type
where t1.type = 'S'
and t2.type is null;
Maybe something like this:
select distinct Person, Type
from table
where (Person, type) in
(select distinct Person, Type
from table
having count(*) = 1)
Added distincts to handle your case.
I like Gary's option for performance reasons, but a simpler method is:
SELECT Person FROM mytable WHERE Type = 'S'
MINUS
SELECT Person FROM mytable WHERE Type IN ('O','R');
精彩评论