Mysql实现Rownum()排序后根据条件获取名次_MySQL

DROP TABLE IF EXISTS `data`;

CREATE TABLE `data` (

`dates` varchar(255) CHARACTER SET utf8 DEFAULT NULL,

`id` int(11) DEFAULT NULL,

`result` varchar(255) CHARACTER SET utf8 DEFAULT NULL

);

INSERT INTO `data` (`dates`, `id`, `result`) VALUES (&＃39;2015109101&＃39;, 1, &＃39;胜&＃39;);

INSERT INTO `data` (`dates`, `id`, `result`) VALUES (&＃39;2015110101&＃39;, 2, &＃39;负&＃39;);

INSERT INTO `data` (`dates`, `id`, `result`) VALUES (&＃39;2015109101&＃39;, 3, &＃39;负&＃39;);

INSERT INTO `data` (`dates`, `id`, `result`) VALUES (&＃39;2015109101&＃39;, 4, &＃39;胜&＃39;);

INSERT INTO `data` (`dates`, `id`, `result`) VALUES (&＃39;2015110101&＃39;, 5, &＃39;胜&＃39;);

INSERT INTO `data` (`dates`, `id`, `result`) VALUES (&＃39;2015109101&＃39;, 6, &＃39;负&＃39;);

INSERT INTO `data` (`dates`, `id`, `result`) VALUES (&＃39;2015109101&＃39;, 7, &＃39;胜&＃39;);

INSERT INTO `data` (`dates`, `id`, `result`) VALUES (&＃39;2015110101&＃39;, 8, &＃39;负&＃39;);

排序

select @rownum:=@rownum+1 AS rownum,id,dates

from

`data`,(SELECT @rownum:=0) r

ORDER BY dates;

结果

条件查询

SELECT rownum,id

from

(select @rownum:=@rownum+1 AS rownum,id,dates

from

`data`,(SELECT @rownum:=0) r

ORDER BY dates)b

WHERE id =2;

结果