34,576
社区成员
发帖
与我相关
我的任务
分享
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;
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*/
select * from tb t where not exists(select 1 from tb where uid=t.uid and score>t.score)