【讨论】请教一个SQL语句的问题

rc_hz

大概的业务需求是这样的：一个学生(student_id)有很多课程(class_id)、每门课程有一个得分(score)。现在想输出每个学生得分最高的那门课程，请问如何用一个SQL实现(Postgresql)。谢谢！

create table my_test(
  id           bigserial,
  student_id   int,
  class_id     varchar(64),
  score        int
);


delete from my_test;
insert into my_test(student_id, class_id, score) values (1, 'c1', 99);
insert into my_test(student_id, class_id, score) values (1, 'c2', 90);
insert into my_test(student_id, class_id, score) values (1, 'c3', 87);

insert into my_test(student_id, class_id, score) values (2, 'c2', 99);
insert into my_test(student_id, class_id, score) values (2, 'c1', 90);
insert into my_test(student_id, class_id, score) values (2, 'c3', 87);

insert into my_test(student_id, class_id, score) values (3, 'c3', 99);
insert into my_test(student_id, class_id, score) values (3, 'c1', 90);
insert into my_test(student_id, class_id, score) values (3, 'c2', 87);


期望的输出为：
1, 'c1', 99
2, 'c2', 99
3, 'c3', 99

fxl201001

select student_id,class_id,max(score)
from my_test
group by  student_id

fxl201001

select student_id,class_id,max(score)
from my_test
group by  student_id

fuiou_cary

业余选手献上一个，大侠有更好的写出来学习下了
select a.student_id,a.class_id,a.score from my_test a right join
(select student_id,max(score) score from my_test group by student_id) b
on a.student_id=b.student_id and a.score=b.score;

fuiou_cary

fxl201001 发表于 2015-1-6 14:58
select student_id,class_id,max(score)
from my_test
group by  student_id

这样不行啊，class_id不一定是最高分那个

rc_hz

fxl201001 发表于 2015-1-6 14:58
select student_id,class_id,max(score)
from my_test
group by  student_id

这个不对：
[Err] ERROR:  column "my_test.class_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4: select student_id,class_id,max(score)

fxl201001

不好意思，看成mysql 了

rc_hz

fuiou_cary 发表于 2015-1-6 14:58
业余选手献上一个，大侠有更好的写出来学习下了
select a.student_id,a.class_id,a.score from my_test a  ...

这个行，但是似乎扫描了两次表。是否有更高效的算法？

Skye_to_Skye

fxl201001 发表于 2015-1-6 14:58
select student_id,class_id,max(score)
from my_test
group by  student_id

这个sql你自己跑过？

fxl201001

在 mysql 下跑过，我以为是  mysql 了 。