开发者

TSQL: finding unique entries in a single table

开发者 https://www.devze.com 2022-12-27 16:04 出处:网络
Consider a table or CTE structured like this: NameNum -------- Abc12 Abc12 XYZ70 XYZ80 XYZ85 Bar开发者_JAVA技巧50

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
0

精彩评论

暂无评论...
验证码 换一张
取 消