mysql选取最小值_选取组内最小值的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/，如需转载，请注明出处，否则将追究法律责任。