TSQL: finding unique entries in a single table
Consider a table or CTE structured like this:
Name Num
---- ----
Abc 12
Abc 12
XYZ 70
XYZ 80
XYZ 85
Bar 开发者_JAVA技巧50
Bar 55
Foo 44
Foo 44
Baz 88
The requirement is to determine the Name where multiple different Nums exist.
The desired resultset is
Name
----
XYZ
Bar
What TSQL statement would you use to derive this resultset?
Update: indeed there could be 2+ entries for a given Name.
Assumes no NULLs in Num column
select
Name
from
MySetObject
group by
name
having
min(num) <> max(num)
-- also COUNT(DISTINCT Num) > 1 achieves the same
精彩评论