oracle 选择表中一列中的重复值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14023608/
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
Select duplicate values in one column in a table
提问by Sawan
I have this table:
我有这张表：
id int, name nvarchar(max), ..............
Example:
例子：
------------------
| id | name |
------------------
| 1 | Mohammed |
| 2 | Mohammed |
| 3 | Sakher |
| 4 | Sakher |
| 5 | Ahmad |
| 6 | Ahmad |
| 11 | Hasan |
| 50 | Hasan |
| 17 | Sameer |
| 19 | Soso |
| 110 | Omar |
| 113 | Omar |
| 220 | Omar |
------------------
I am trying to write a query to result this:
我正在尝试编写一个查询来产生这个结果：
id1 int , id2 int , name nvarchar(max)
Example:
例子：
------------------------
| id1 | id2 | name |
------------------------
| 1 | 2 | Mohammed |
| 3 | 4 | Sakher |
| 5 | 6 | Ahmad |
| 11 | 50 | Hasan |
| 110 | 113 | Omar |
| 110 | 220 | Omar |
| 113 | 220 | Omar |
------------------------
Return the duplicates in one of the columns. I prefer SQL Server query or standard ANSI SQL one.
返回其中一列中的重复项。我更喜欢 SQL Server 查询或标准 ANSI SQL 查询。
回答by ederbf
This query returns what you ask for. Comparing n1.id > n2.id is better than doing it like n1.id != n2.id because this way you would get every pair twice (the second time, reversed):
此查询返回您要求的内容。比较 n1.id > n2.id 比做 n1.id != n2.id 更好，因为这样你会得到每对两次（第二次，相反）：
SELECT
n1.id as Col1, n2.id as Col2, n1.name
FROM
Names n1, Names n2
WHERE
n1.name = n2.name
AND n1.id > n2.id
回答by SWeko
Select count(*), name
from someTable
group by name
having count(*) > 1
Will give you all the names that have duplicates, along with the number of duplicates, and might be more useful that the one you have requested. To achieve this, you could do the following:
将为您提供所有重复的名称以及重复的数量，并且可能比您请求的名称更有用。为此，您可以执行以下操作：
Select a.id, b.id, a.name
from someTable a
inner join someTable b
on a.id <> b.id and a.name = b.name
