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

最新推荐文章于 2022-12-18 22:49:58 发布

翛宸

最新推荐文章于 2022-12-18 22:49:58 发布

阅读量6.3k

点赞数 1

分类专栏：数据库文章标签： mysql 排序名次

本文链接：https://blog.csdn.net/fxfffp/article/details/51354004

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

初始化表结构

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;