Maison > Article > base de données > Mysql实现Rownum()排序后根据条件获取名次_MySQL
Mysql实现Rownum()排序后根据条件获取名次_MySQL
- WBOYoriginal
- 2016-06-08 08:50:362527parcourir
初始化表结构
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 ('2015109101', 1, '胜');
INSERT INTO `data` (`dates`, `id`, `result`) VALUES ('2015110101', 2, '负');
INSERT INTO `data` (`dates`, `id`, `result`) VALUES ('2015109101', 3, '负');
INSERT INTO `data` (`dates`, `id`, `result`) VALUES ('2015109101', 4, '胜');
INSERT INTO `data` (`dates`, `id`, `result`) VALUES ('2015110101', 5, '胜');
INSERT INTO `data` (`dates`, `id`, `result`) VALUES ('2015109101', 6, '负');
INSERT INTO `data` (`dates`, `id`, `result`) VALUES ('2015109101', 7, '胜');
INSERT INTO `data` (`dates`, `id`, `result`) VALUES ('2015110101', 8, '负');
排序
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;
结果
