oracle 如何在多个不同的列上应用 Count 并使用 Hading 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24004235/
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
How to apply Count on multiple distinct columns and use Having clause
提问by amateur
I would like to do something like this , but getting an error please suggest some good methods?
我想做这样的事情，但遇到错误请提出一些好的方法？
select A,B,C, count(Distinct A,B,C)
from table_name
group by A,B,C
having count(Distinct A,B,C) > 1
Basically i have an index on the columns(A,B,C), and some rows doesnt have this unique combination set, So I'm trying a query similar to identify the rows which disobeys the unique constraint. PLease let me know if there is a best way
基本上我在列（A、B、C）上有一个索引，有些行没有这个唯一的组合集，所以我正在尝试一个类似的查询来识别不遵守唯一约束的行。请让我知道是否有最好的方法
回答by juergen d
If you group by these columns then you already only get those unique records and then you can use count(*)to get how many duplicates you have
如果您按这些列分组，那么您已经只获得了那些唯一记录，然后您可以使用它count(*)来获取您有多少重复记录
select A,B,C, count(*)
from table_name
group by A,B,C
HAVING count(*) > 1
回答by woot
What @jurgend said is right, and you can further find the exact rows (I'm assuming there are more fields to look at, including maybe a PK) by doing
@jurgend 说的是对的，您可以通过执行进一步找到确切的行（我假设有更多的字段需要查看，可能包括一个 PK）
SELECT *
FROM table_name
WHERE (A,B,C) IN (
SELECT A, B, C
FROM table_name
GROUP BY A, B, C
HAVING COUNT(*) > 1
)
A Tuple INlist query works in Oracle, although not all other DBMS.
一个Tuple IN清单查询工作在Oracle中，虽然不是所有的其他DBMS。
