SQL 如何检索在oracle中出现两次以上的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9582538/
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 retrieve records which occurred more than twice in oracle?
提问by Ashish dmc4
I have this table
我有这张桌子
create table student (
stu_id int,
s_name nvarchar(max),
s_subject nvarchar(max),
)
and this as data
这作为数据
insert into student values(123,'pammy','English');
insert into student values(123,'pammy','Maths');
insert into student values(123,'pammy','Chemistry');
insert into student values(124,'watts','Biology');
insert into student values(125,'Tom','Physics');
insert into student values(125,'Tom','Computer';
insert into student values(125,'Tom','ED';
so i wanted to retrieve records which has occurred more than twice. my code is
所以我想检索发生了两次以上的记录。我的代码是
select stu_id,s_Name
from student
group by stu_id,s_Name
having count(stu_id) >2 ;
the result was perfect.
结果很完美。
but when i want s_subjectas well it says no rows selected. I dont know why.
但是当我也想要时s_subject，它说没有选择行。我不知道为什么。
select stu_id,s_Name,s_subject
from student
group by stu_id,s_Name,s_subject
having count(stu_id) >2 ;
回答by John N
It's because none of your students have more than one record per subject.
这是因为您的学生每个科目的记录都不超过一个。
select stu_id,s_Name,s_subject
from student
group by stu_id,s_Name,s_subject
having count(stu_id) >2 ;
This code asks for records that occur more than twice that have the same Student ID, name and subject. None of the records in your sample meet this.
此代码要求出现两次以上且具有相同学生 ID、姓名和科目的记录。您的样本中没有任何记录符合这一点。
If, however, what you actually want is the ID, name and subjects of any student that is taking more than two classes, this can accomplished quite easily.
但是，如果您真正想要的是参加两门以上课程的任何学生的 ID、姓名和科目，这很容易实现。
Using a slightly modified version of your initial SQL as a filter, we get this:
使用对初始 SQL 稍作修改的版本作为过滤器，我们得到：
select stu_id, name, subject
from student
where stu_id in ( select stu_id
from student
group by stu_id
having count(stu_id) >2 );
Hope this helps.
希望这可以帮助。
回答by Ashish dmc4
As you are grouping by all column exists in table, it will generate unique rows(record with one frequency row).As you already selecting rows having more than 2, it will not have the records with frequency 2. If you will do with count=1, you will get all rows with count=1,
当您按表中存在的所有列进行分组时，它将生成唯一的行（具有一个频率行的记录）。由于您已经选择了超过 2 个的行，因此它将没有频率为 2 的记录。如果您将使用计数=1，您将获得 count=1 的所有行，
select stu_id,s_Name,s_subject
from student
group by stu_id,s_Name,s_subject
having count(stu_id) =1 ;
The output will be:
输出将是：
stu_id s_Name s_subject
----------- -------------
123 pammy Chemistry
123 pammy English
123 pammy Maths
124 watts Biology
125 Tom Computer
125 Tom ED
125 Tom Physics
