选取组内最小值的sql

今天群里有个朋友提问如下：
查询每一门课程的成绩均大于等于80分的学生的学号、姓名和性别
请问SQL语句怎么写啊
好久没写sql了，这个我觉得就是求组内最小值吧。就写个试试。

drop table student;
create table student
(
sno number,
sname varchar2(20),
ssex varchar2(4)
);
drop table course;
create table course
(
sno number,
cno number,
grade number
);

insert into student values(1,'yallonking','f');
insert into student values(2,'liangbi','m');
insert into student values(3,'dapeng','f');
insert into student values(4,'dux','m');
commit;

insert into course values(1,1,89);
insert into course values(1,2,80);
insert into course values(1,3,90);

insert into course values(2,1,89);
insert into course values(2,2,79);
insert into course values(2,3,70);

insert into course values(3,1,90);
insert into course values(3,2,80);
insert into course values(3,3,81);

commit;

SQL> select * from student;

       SNO SNAME                SSEX
---------- -------------------- ----
         1 yallonking           f
         2 liangbi              m
         3 dapeng               f
         4 dux                  m

SQL> select * from course;

       SNO        CNO      GRADE
---------- ---------- ----------
         1          1         89
         1          2         80
         1          3         90
         2          1         89
         2          2         79
         2          3         70
         3          1         90
         3          2         80
         3          3         81

9 rows selected.

SQL> select sno, sname, ssex
  2    from sys.student
  3   where sno in (select distinct sno
  4                   from (select c.*,
  5                                row_number() over(partition by sno order by grade) rn
  6                           from sys.course c) d
  7                  where rn = 1
  8                    and grade >= 80);

       SNO SNAME                SSEX
---------- -------------------- ----
         1 yallonking           f
         3 dapeng               f

貌似达到要求了，不过感觉写法应该有很多。哎...自己的sql给颓废掉了..

--发到群里，没想到提问者是mysql库，用不了oracle的分析函数，遂改为以下
SQL> select sno, sname, ssex
  2    from sys.student
  3   where sno in (select distinct sno
  4                   from (select sno, min(grade) min_grade
  5                           from sys.course c
  6                          group by sno)
  7                  where min_grade >= 80);

       SNO SNAME                SSEX
---------- -------------------- ----
         1 yallonking           f
         3 dapeng               f

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26143577/viewspace-749247/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26143577/viewspace-749247/