数据:表score
id score1 score2
1 10 30
2 20 20
3 30 10结果:
id rank1 rank2
1 3 1
2 2 2
3 1 3结果中的rank为对应score在上表中的名次
id score1 score2
1 10 30
2 20 20
3 30 10结果:
id rank1 rank2
1 3 1
2 2 2
3 1 3结果中的rank为对应score在上表中的名次
是你说的..."不论多长都可以"
select r1.id, rank1, rank2
from
(
select score1, score2,id, @a:=@a+1 as rank1
from test1, ( select @a:=0 ) x
where 1
order by score1 desc
) r1,
(
select score1, score2,id, @b:=@b+1 as rank2
from test1, ( select @b:=0 ) x
where 1
order by score2 desc
) r2
where r1.id=r2.id
;