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

来源：互联网发布：linux vi 命令模式编辑：程序博客网时间：2024/06/05 00:55

初始化表结构

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,idfrom    (select @rownum:=@rownum+1 AS rownum,id,dates     from    `data`,(SELECT @rownum:=0) r     ORDER BY dates)b     WHERE id =2;

结果

这里写图片描述

写在最后的话

获取你有更好的方法在mysql中来实现Rownum()，欢迎不吝赐教。

1 0