使用count distinct来查找字段中具有2个或更多不同值的记录

I have a simple question: How can I use Count(Distinct) in SQL (Oracle to be exact) to return only the rows where there are two or more different values in a given field.

我有一个简单的问题：我如何在SQL中使用Count（Distinct）（确切地说是Oracle）只返回给定字段中有两个或更多不同值的行。

This is easier understood by example:

通过示例更容易理解：

ACCOUNT     SALESMAN
123         Abc
123         Abc

246         Abc
246         Def
246         Def

369         Hij

456         Abc
456         Def

In this example, the only Accounts with 2 different sales reps would be 246 and 456, and thus, I'd want the query's result to just show the the accounts shared by 2 or more salesmen:

在此示例中，具有2个不同销售代表的唯一帐户将是246和456，因此，我希望查询的结果仅显示由2个或更多销售人员共享的帐户：

ACCOUNT     SALESMAN
246         Abc
246         Def
456         Abc
456         Def

Thanks.

谢谢。

3 个解决方案

#1

use having :

使用：

select distinct account,salesman 
from MyTable where account in
(
    select account
    from MyTable
    group by account
    having count(distinct salesman) >= 2
)
order by 1,2

Here is a demonstration.

这是一个演示。

#2

As the other answer has indicated you need to use HAVING, but not in the manor indicated. You need to join back to the original table after using HAVING

正如另一个答案表明你需要使用HAVING，但不是在庄园指示。使用HAVING后，您需要连接回原始表

SELECT  DISTINCT T.Account, T.SalesMan
FROM    T
        INNER JOIN
        (   SELECT  Account
            FROM    T
            GROUP BY Account
            HAVING COUNT(DISTINCT SalesMan) > 1
        ) Dupes
            ON Dupes.Account = T.Account

SQL Fiddle

SQL小提琴

#3

You can do this with a simple GROUP BY/HAVING query:

您可以使用简单的GROUP BY / HAVING查询执行此操作：

select account
from t
group by account
having count(distinct salesperson) > 1

This returns the accounts, so the result is different from what you specify. One way to get the sales people is to use listagg:

这将返回帐户，因此结果与您指定的结果不同。获得销售人员的一种方法是使用listagg：

select account, listagg(salesperson, ',')
from t
group by account
having count(distinct salesperson) > 1

Otherwise, Gareth's answer returns the results the way you specified in the question.

否则，Gareth的答案将按照您在问题中指定的方式返回结果。

#1