MySQL社区

标题: MYSQL 排名 [打印本页]

作者: u332744 时间: 2013-2-6 17:50
标题: MYSQL 排名

I hava a table like this:

id,name,score,rank

1,anna,80,0

2,qera,65,0

6,kero,90,0

10,rosa,95,0

what I would like to do is to update the rank position. I have this,

update mytable set rank= 1 where max(score);

update mytable set rank= 2 where max(score-1) ;

....

the result should be like this:

id,name,score,rank

1,anna,80,3

2,qera,65,4

6,kero,90,2

10,rosa,95,1

作者: 蓝寅舞九天 时间: 2013-7-14 12:35
if your table is named test1 ,then
update test1 x
inner join ( SELECT
a.id,count(*) as rank from  test1 a
inner join  test1 b on a.score<=b.score
group by  a.id) y
on x.id =y.id
set x.rank =y.rank

欢迎光临 MySQL社区 (http://mysqlpub.com/)