表virtualuser
id name
1 shark
2 Michael
3 magic表virtualscore
id userid score scoretime
1 1 20 20080301
2 1 15 20080301
3 3 10 20080301
4 2 35 20080302想得到得分最高的用户name和分数
如
shark 35
michael 35
用一句sql语句如何实现?
我分数不多,请见谅
id name
1 shark
2 Michael
3 magic表virtualscore
id userid score scoretime
1 1 20 20080301
2 1 15 20080301
3 3 10 20080301
4 2 35 20080302想得到得分最高的用户name和分数
如
shark 35
michael 35
用一句sql语句如何实现?
我分数不多,请见谅
(
select a.name,b.score
from virtualuser a,virtualscore b
order by b.score
)where rownum=1
如果有并列最高分,像我的例子,该如何解决?
能说得详细一些么?
virtualscore v,virtualuser vu,
(select max(score),scoretime from virtualscore
group by scoretime) vs
where v.userid = vu.id
and v.score = vs.score and v.scoretime = vs.scoretime或者 分析函数 rank或者dense_rank
rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,
select vu.name,v.score
from virtualuser vu
(select userid ,score,scoretime,rank() over (partition by scoretime order by score desc ) as rn from virtualscore) v
where v.userid = vu.id
and v.rn=1
(select userid,score from virtualscore where score in(select max(score) from virtualscore)) t1
where a.id=t1.userid and b.score=t1.score
from virtualuser vu,virtualscore vs
where vu.id=vs.userid and
vs.scoure in(select max(scoure)
from virtualscore)
所以首先要
select sum(score) s, userid u
from virtualscore
group by userid 然后从这个结果集中找出得分最高的用户(可能有并列)
最后得到这些用户的名字和得分
-----------------
不就是加个where条件吗?还要别人写出来“rank() over”可以搞定。
select userid,sum(score) score from virtualscore group by userid) s
where u.id = s.userid and
s.score =
(select max(a.x) from (select sum(score) x from virtualscore group by userid) a)
select userid,score,scoretime,rk from(
select userid,score,scoretime,rank()over(order by score desc)as rk from(
select userid,sum(score) as score,scoretime from score group by userid,scoretime)
) where rk=1
),users u
where u.id=userid;