欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-03-26 17:34:12
...
初始化表结构

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;

结果

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

条件查询

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;

结果

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