假设我有这个表:
ID Score
1 345
2 5
3 76
4 45
5 85
6 175
7 209
这是我的问题:
SELECT * FROM posts ORDER BY Score DESC
如何获得每行的级别/位置?
您可以使用类似@OMG Ponies的技术链接到上面,如下所示:
SELECT posts.*, @row:=@row+1 as 'rank' FROM posts, (SELECT @row:=0) r ORDER BY posts.Score DESC
如果运行以下代码,您将看到新列为Position
,与Rank
相同
SELECT ID, Score,
(SELECT COUNT(distinct u2.Score) FROM myTable u2
WHERE
u2.Score > u1.Score) + 1 AS position FROM myTable u1
ORDER BY position
如果你有任何问题,请告诉我。。。