这个视图怎么建

[red_berries]

比如表结构是uid,sid,score，分别代表学生ID,课程ID,课程分数,如果建一个每个学生最高得分的视图，比如如下数据
uid sid score
1 2 80
1 3 100
2 2 90
2 3 80
视图内容应该为
uid sid score
1 3 100
2 2 90

我想到的写法是

create view v_best_score(uid, sid, score) as select a.uid, a.sid, a.score from t_score a 
 right join (select uid as bestid, max(score) as bestscore from t_score group by uid)t
 on a.uid=t.lastuid and a.score=t.bestscore;

可是提示说视图中的表不能是临时表，怎么搞？谢谢大家

[水族杰纶]

select * from tb t where not exists(select 1 from tb where uid=t.uid and score>t.score)

[水族杰纶]

if object_id('tb')is not null drop table tb
go
create table tb(uid int ,sid int , score int) 
insert tb select 1  ,2 ,   80 
insert tb select 1 , 3  ,  100 
insert tb select 2,  2   , 90 
insert tb select 2,  3   , 80 
select * from tb t where not exists(select 1 from tb where uid=t.uid and score>t.score)
/*
uid         sid         score       
----------- ----------- ----------- 
1           3           100
2           2           90*/

[nj_1st_excellence]

create table tscore(
uid int,
sid int,
score int
)
insert tscore select 1,2,80
insert tscore select 1,3,100
insert tscore select 2,2,90
insert tscore select 2,3,80

create view v_best_score(uid, sid, score) as
select * from tscore as outtb where not exists(select * from tscore where uid=outtb.uid and score>outtb.score)

select * from v_best_score

drop view v_best_score
drop table tscore

执行结果
------------------------------------------------
1 3 100
2 2 90

[red_berries]

谢谢