一个多表查询问题

create table stu
(
stuid int identity(1,1) primary key,
stuname varchar(30)
)
create table kemu
(
Keid int identity(1,1) primary key,
kename varchar(30)
)
create table score
(
stuid int references stu(stuid),
keid  int references kemu(keid),
score int
)

insert into stu values('hu')
insert into stu values('wang')
insert into stu values('liu')
insert into kemu values('语文')
insert into kemu values('数学')
insert into kemu values('英语')
insert into score values(3,1,88)
insert into score values(3,2,89)
insert into score values(3,2,50)
insert into score values(3,3,70)
insert into score values(3,3,40)
insert into score values(2,2,60)
insert into score values(2,3,90)
insert into score values(1,1,40)
insert into score values(1,3,40)
insert into score values(2,3,89)

select * from stu

select * from score
select * from kemu

select * from (select keid,max(score)as max_score from score group by keid) a,score b
where a.max_score=b.score and a.keid=b.keid