在 Oracle SQL 中查找不唯一的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26101767/
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
Find not unique rows in Oracle SQL
提问by Süniúr
I have a question wich looks easy but I cant figure it out.
我有一个看起来很简单的问题，但我无法弄清楚。
I have smth the following :
我有以下几点：
Name Zipcode
ER 5354
OL 1234
AS 1234
BH 3453
BH 3453
HZ 1234
I want to find those rows where the ID not define clearly one row.
我想找到那些 ID 没有明确定义一行的行。
So here I want to see :
所以我想在这里看到：
OL 1234
AS 1234
HZ 1234
Or simply the zipcode enought.
或者只是邮政编码就够了。
I am sorry i forget to mention an important part. If the name is the same its not a problem, only if there are different names for the same zipcode.So this mean : BH 3453 does not return
很抱歉我忘记提到一个重要的部分。如果名称相同，则没有问题，仅当同一个邮政编码有不同的名称时。所以这意味着：BH 3453 不会返回
回答by vc 74
I think this is what you want
我想这就是你想要的
select zipcode
from yourTable
group by zipcode
having count(*) > 1
It selects the zipcodes associated to more than one record
它选择与多个记录相关联的邮政编码
to answer your updated question:
回答您更新的问题：
select zipcode
from
(
select name, zipcode
from yourTable
group by name, zipcode
)
group by zipcode
having count(*) > 1
should do it. It might not be optimal in terms of performance in which case you could use window functions as suggested by @a1ex07
应该这样做。它在性能方面可能不是最佳的，在这种情况下，您可以使用@a1ex07 建议的窗口函数
回答by neshkeev
Try this:
尝试这个：
select yt.*
from YOUR_TABLE yt
, (select zipcode
from YOUR_TABLE
group by zipcode
having count(*) > 1
) m
where yt.zipcode = m.zipcode
回答by a1ex07
If you need just zipcode, use vc 74's solution. For all columns , solution based on window functions supposedly outperforms self join approach:
如果您只需要邮政编码，请使用vc 74的解决方案。对于所有列，基于窗口函数的解决方案应该优于自连接方法：
SELECT a.zipcode, a.name
FROM
(
SELECT zipcode, name, count(1) over(partition by zipcode) as cnt
FROM your_table
)a
WHERE a.cnt >1
