oracle 在oracle中查找重复值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14527903/
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 duplicate values in oracle
提问by Naty Bizz
I'm using this query to find duplicate values in a table:
我正在使用此查询在表中查找重复值：
select col1,
count(col1)
from table1
group by col1
having count (col1) > 1
order by 2 desc;
But also I want to add another column from the same table, like this:
但我也想从同一个表中添加另一列，如下所示：
select col1,
col2,
count(col1)
from table1
group by col1
having count (col1) > 1
order by 2 desc;
I get an ORA-00979error with that second query
我得到一个ORA-00979与第二个查询错误
How can I add another column in my search?
如何在搜索中添加另一列？
回答by rs.
Your query should be
您的查询应该是
SELECT * FROM (
select col1,
col2,
count(col1) over (partition by col1) col1_cnt
from table1
)
WHERE col1_cnt > 1
order by 2 desc;
回答by Allan
Presumably you want to get col2for each duplicate of col1that turns up. You can't really do that in a single query^. Instead, what you need to do is get your list of duplicates, then use that to retrieve any other associated values:
大概你想col2为每一个出现的重复获得col1。您无法在单个查询中真正做到这一点^。相反，您需要做的是获取重复项列表，然后使用它来检索任何其他关联值：
select col1, col2
from table1
where col1 in (select col1
from table1
group by col1
having count (col1) > 1)
order by col2 desc
^ Okay, you can, by using analytic functions, as @rs. demonstrated. For this scenario, I suspect that the nested query will be more efficient, but both should give you the same results.
^ 好的，您可以通过使用解析函数作为@rs。证明了。对于这种情况，我怀疑嵌套查询会更有效，但两者都应该为您提供相同的结果。
Based on comments, it seems like you're not clear on why you can't just add the second column. Assume you have sample data that looks like this:
根据评论，您似乎不清楚为什么不能只添加第二列。假设您有如下所示的示例数据：
Col1 | Col2
-----+-----
1 | A
1 | B
2 | C
2 | D
3 | E
If you run
如果你跑
select Col1, count(*) as cnt
from table1
group by Col1
having count(*) > 1
then your results will be:
那么你的结果将是：
Col1 | Cnt
-----+-----
1 | 2
2 | 2
You can't just add Col2to this query without adding it to the group byclause because the database will have no way of knowing which value you actually want (i.e. for Col1=1 should the DB return 'A' or 'B'?). If you add Col2 to the group byclause, you get the following:
您不能只添加Col2到此查询而不将其添加到group by子句中，因为数据库将无法知道您实际想要哪个值（即对于 Col1=1，数据库应该返回“A”还是“B”？）。如果将 Col2 添加到group by子句中，则会得到以下结果：
select Col1, Col2, count(*) as cnt
from table1
group by Col1, Col2
having count(*) > 1
Col1 | Col2 | Cnt
-----+------+----
[no results]
This is because the count is for each combination of Col1and Col2(each of which are unique).
这是因为，计数为的每个组合Col1和Col2（其中的每一个是唯一的）。
Finally, by using either a nested query (as in my answer) or an analytic function (as in @rs.'s answer), you'll get the following result (query changed slightly to return the count):
最后，通过使用嵌套查询（如我的答案）或分析函数（如@rs. 的答案），您将获得以下结果（查询略有更改以返回计数）：
select t1.col1, t1.col2, cnt
from table1 t1
join (select col1, count(*) as cnt
from table1
group by col1
having count (col1) > 1) t2
on table1.col1 = t2.col1
Col1 | Col2 | Cnt
-----+------+----
1 | A | 2
1 | B | 2
2 | C | 2
2 | D | 2
回答by Donato Szilagyi
You should list all selected columns in the group by clause as well.
您还应该列出 group by 子句中的所有选定列。
select col1,
col2,
count(col1)
from table1
group by col1, col2
having count (col1) > 1
order by 2 desc;
回答by Vimalnath
Cause of Error
错误原因
You tried to execute an SQL SELECT statement that included a GROUP BY function (ie: SQL MIN Function, SQL MAX Function, SQL SUM Function, SQL COUNT Function) and an expression in the SELECT list that was not in the SQL GROUP BY clause.
您试图执行包含 GROUP BY 函数（即：SQL MIN 函数、SQL MAX 函数、SQL SUM 函数、SQL COUNT 函数）和 SELECT 列表中不在 SQL GROUP BY 子句中的表达式的 SQL SELECT 语句。
select col1,
col2,
count(col1)
from table1
group by col1,col2
having count (col1) > 1
order by 2 desc;
