SQL Oracle：在选择查询中查找重复行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13776123/
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
Oracle: find duplicate rows in select query
提问by user613114
My SQL query returns results with 4 columns "A", "B", "C", "D".
我的 SQL 查询返回带有 4 列“A”、“B”、“C”、“D”的结果。
Suppose the results are:
假设结果是：
A B C D
1 1 1 1
1 1 1 2
2 2 2 1
Is it possible to get the count of duplicate rows with columns "A", "B", "C" in each row.
是否有可能获得每行中带有“A”、“B”、“C”列的重复行的计数。
e.g. the expected result is:
例如，预期结果是：
A B C D cnt
1 1 1 1 2
1 1 1 2 2
2 2 2 1 1
I tried using count(*) over. But it returns me the total number of rows returned by the query. Another information is that in example I have mentioned only 3 columns based on which I need to check the count. But my actual query has such 8 columns. And number of rows in database are huge. So I think group by will not be a feasible option here. Any hint is appreciable. Thanks.
我尝试使用 count(*) 结束。但它返回查询返回的总行数。另一个信息是，在示例中，我只提到了 3 列，我需要根据这些列检查计数。但我的实际查询有这样的 8 列。并且数据库中的行数很大。所以我认为 group by 在这里不是一个可行的选择。任何提示都是可观的。谢谢。
回答by Stefan
Maybe too late, but probably the count over as analytic function (aka window function) within oracle helps you. When I understand your request correctly, this should solve your problem :
也许为时已晚，但可能在 oracle 中作为分析函数（又名窗口函数）的计数可以帮助您。当我正确理解您的请求时，这应该可以解决您的问题：
create table sne_test(a number(1)
,b number(1)
,c number(1)
,d number(1)
,e number(1)
,f number(1));
insert into sne_test values(1,1,1,1,1,1);
insert into sne_test values(1,1,2,1,1,1);
insert into sne_test values(1,1,2,4,1,1);
insert into sne_test values(1,1,2,5,1,1);
insert into sne_test values(1,2,1,1,3,1);
insert into sne_test values(1,2,1,2,1,2);
insert into sne_test values(2,1,1,1,1,1);
commit;
SELECT a,b,c,d,e,f,
count(*) over (PARTITION BY a,b,c)
FROM sne_test;
A B C D E F AMOUNT
-- -- -- -- -- -- ------
1 1 1 1 1 1 1
1 1 2 4 1 1 3
1 1 2 1 1 1 3
1 1 2 5 1 1 3
1 2 1 1 3 1 2
1 2 1 2 1 2 2
2 1 1 1 1 1 1
回答by surendra
To find duplicates you must group the data based on key column
要查找重复项，您必须根据键列对数据进行分组
select
count(*)
,empno
from
emp
group by
empno
having
count(*) > 1;
This allows you to aggregate by empnoeven when multiple records exist for each category (more than one).
empno即使每个类别存在多个记录（不止一个），这也允许您进行聚合。
回答by fthiella
You have to use a subquery where you get the count of rows, grouped by A, B and C. And then you join this subquery again with your table (or with your query), like this:
您必须使用一个子查询来获取按 A、B 和 C 分组的行数。然后您再次将这个子查询与您的表（或您的查询）连接起来，如下所示：
select your_table.A, your_table.B, your_table.C, your_table.D, cnt
from
your_table inner join
(SELECT A, B, C, count(*) as cnt
FROM your_table
GROUP BY A, B, C) t
on t.A = your_table.A
and t.B = your_table.B
and t.C = your_table.C
