oracle 使用 count distinct 查找字段中具有 2 个或更多不同值的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12534177/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using count distinct to find records with 2 or more different values in a field
提问by Yaaqov
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（准确地说是 Oracle）中使用 Count(Distinct) 来仅返回给定字段中有两个或多个不同值的行。
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 differentsales 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.
谢谢。
回答by Grisha Weintraub
use having:
使用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.
这是一个演示。
回答by GarethD
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
回答by Gordon Linoff
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 的回答会按照您在问题中指定的方式返回结果。
