oracle 如何找到具有相同（某些 x 值）值重复多次的列？需要返回那些行。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8326498/
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 find Column with same (some x value) value repeated more than once? Needs to return those rows.
提问by Chandra Sekhar
There is a table called contactswith columns id, name, address, ph_noetc.
I need to find out rows with the same name, if the rows count is more than 1, show those rows.
有一个contacts带有列id, name, address, ph_no等的表。
我需要找出具有相同name的行，如果行数大于 1，则显示这些行。
For example:
Table: contacts
例如：
表：contacts
id--------name--------address---------ph_no--------
111 apple U.K 99*******
112 banana U.S 99*******
123 grape INDIA 99*******
143 orange S.AFRICA 99*******
152 grape KENYA 99*******
For the above table I need to get rows with same column namedata like the below:
对于上表，我需要获取具有相同列name数据的行，如下所示：
id--------name--------address---------ph_no--------
123 grape INDIA 99*******
152 grape KENYA 99*******
I need to get the rows based on the name what I given as argument like below examplesyntax:
我需要根据我作为参数给出的名称获取行，如下example语法：
select * from contacts where name='grape' and it's count(*) >1 return those rows.
How can I achieve the solution for above problem.
我怎样才能实现上述问题的解决方案。
回答by Ben
As @vc74 suggests analytic functions would work work a lot better here; especially if your data has any volume.
正如@vc74 建议的那样，分析函数在这里工作得更好；特别是如果您的数据有任何数量。
select id, name, address, ph_no ...
from ( select c.*, count(name) over ( partition by name ) as name_ct
from contacts c )
where name_ct > 1
;
EDIT
编辑
restricting on specific names the table contactsshould really have an index on nameand the query would look like this:
限制特定名称表contacts应该真正有一个索引，name查询将如下所示：
select id, name, address, ph_no ...
from ( select c.*, count(name) over ( partition by name ) as name_ct
from contacts c
where name = 'grape' )
where name_ct > 1
;
回答by vc 74
select id, name, address, ph_no
from contacts
where name in
(
select name from contacts
group by name
having count(*) > 1
)
If you have access to Oracle's analytical functions there might be a more straightforward way
如果您可以访问 Oracle 的分析功能，则可能有更直接的方法
回答by Zsolt Botykai
select *
from contacts c
where c.name in ( select cc.name
from contacts
group by cc.name
having count(1) > 1 );
