選取組內最小值的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/，如需轉載，請註明出處，否則將追究法律責任。