oracle oracle表中重复的总数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12143176/
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
total number of duplicates in oracle table
提问by user1627796
I have a table like below
我有一张如下表
SUBJECT Years MARKS
AB 1 20
AB 1 25
AC 1 20
AC 1 30
AC 1 40
AD 1 20
I only need count of duplicates (subject||Year), expected answer is 2 and not
我只需要重复数（主题||年），预期答案是 2 而不是
AB1 -- 2
AC1 -- 3
回答by Rajesh Chamarthi
That would be one more query on top of the duplicates query...
那将是重复查询之上的另一个查询......
select subject, year, count(*)
from table1
group by subject, year
having count(*) > 1
will give you all the results with counts. Another count over this..
会给你带计数的所有结果。另一个计数。
select count(*)
from (
select subject, year, count(*)
from table1
group by subject, year
having count(*) > 1
)
should give you the number of records which have one or more duplicates.
应该给你有一个或多个重复的记录数。
回答by vikiiii
select subject,years, count(*)
from table
group by subject,years
having count (*) > 1;
回答by Lord Peter
Just use an inline view over the query you used to get your example (AB1, AC1) like this:
只需在用于获取示例（AB1，AC1）的查询上使用内联视图，如下所示：
select count (*) from (select subject, years from t group by subject, years having count (*) > 1)
