Oracle：在select查询中查找重复行 [英] Oracle: find duplicate rows in select query

问题描述

我的SQL查询返回4列A，B，C，D的结果。

My SQL query returns results with 4 columns "A", "B", "C", "D".

假设结果是：

A    B    C    D
1    1    1    1
1    1    1    2
2    2    2    1

可以获取每行中具有列A，B，C

Is it possible to get the count of duplicate rows with columns "A", "B", "C" in each row.

预期的结果是：

A    B    C    D    cnt
1    1    1    1    2
1    1    1    2    2
2    2    2    1    1

我尝试使用count（*）。但它返回我查询返回的总行数。
另一个信息是，在示例中，我只提到了3列，我需要检查计数。但我的实际查询有这样的8列。数据库中的行数很大。所以我认为group by不会是一个可行的选择在这里。
任何提示都是可见的。
谢谢。

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.

推荐答案

也许太晚了，但可能是算作分析函数帮助你。当我正确理解您的请求时，这应该可以解决您的问题：

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 :

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

这篇关于Oracle：在select查询中查找重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！