If I have a table of random values in a sql table, how can I export and display them as a raking rather than the absolute value... for example.. if the three val开发者_StackOverflow中文版ues are 30 85 and 90 how do i get 30 do display as 1 or 1st, 85 as 2 or 2nd etc
MySQL doesn't have analytic function support (ROW_NUMBER, RANK, DENSE_RANK), which is generally what you'd use for requirements like these.
Use:
SELECT @rownum := @rownum + 1 AS ranking
FROM YOUR_TABLE t
JOIN (SELECT @rownum := 0) r
ORDER BY t.value
Example:
SELECT x.num,
@rownum := @rownum + 1 AS ranking
FROM (SELECT 30 AS num
UNION ALL
SELECT 85
UNION ALL
SELECT 90) x
JOIN (SELECT @rownum := 0) r
ORDER BY x.num
You could use:
SELECT (SELECT COUNT(*)
FROM YOUR_TABLE b
WHERE b.value <= a.value) AS ranking
FROM YOUR_TABLE a
...but duplicates would have the same ranking value. And you have to make sure the value comparison is in the correct direction.
Try:
set @i = 0;
select myValue,
@i:=@i+1 as Ranking
from myTable
order by myValue ASC
精彩评论