with t_stu as (select '王五' name , 'English' as course_name, 78 grade from dual union all select '王五', 'Math', 87 from dual union all select '王五', 'Chinese', 75 from dual union all select '张三', 'English', 87 from dual union all select '张三', 'Math', 82 from dual union all select '张三', 'Chinese', 80 from dual union all select '李四', 'English', 79 from dual union all select '李四', 'Math', 80 from dual union all select '李四', 'Chinese', 70 from dual) select a.* from t_stu a, (select name From t_stu where grade >=80 group by name having count(1)>2) b where a.name=b.name;NAME COURSE_NAME GRADE 张三 English 87 张三 Math 82 张三 Chinese 80
select * from student st, ( select name,sum(grade)/count(*) sum_c from student group by name )t where t.sum_c >=80 and st.name=t.name;
(select '王五' name , 'English' as course_name, 78 grade from dual union all
select '王五', 'Math', 87 from dual union all
select '王五', 'Chinese', 75 from dual union all
select '张三', 'English', 87 from dual union all
select '张三', 'Math', 82 from dual union all
select '张三', 'Chinese', 80 from dual union all
select '李四', 'English', 79 from dual union all
select '李四', 'Math', 80 from dual union all
select '李四', 'Chinese', 70 from dual)
select a.*
from t_stu a,
(select name From t_stu
where grade >=80
group by name having count(1)>2) b
where a.name=b.name;NAME COURSE_NAME GRADE
张三 English 87
张三 Math 82
张三 Chinese 80
from
student st,
(
select name,sum(grade)/count(*) sum_c
from student
group by name
)t
where t.sum_c >=80 and st.name=t.name;