Initialize table structure
DROP TABLE IF EXISTS ' data ';create TABLE Span class= "hljs-string" > ' data ' ( ' dates ' varchar (255 ) character set UTF8 default null , Span class= "hljs-string" > ' id ' int (11 ) Span class= "Hljs-keyword" >default null , varchar (255 ) character set UTF8 default null ); INSERT into ' data ' (' dates ', ' id', ' result ') VALUES (' 2015109101', 1, ' Sheng '); INSERT into ' data ' (' dates ', ' id ', ' result ') VALUES (' 2015110101 ', 2, ' negative '); INSERT into ' data ' (' dates ', ' id', ' result ') VALUES (' 2015109101 ', 3, ' negative '); INSERT into ' data ' (' dates ', ' id ', ' result ') VALUES (' 2015109101', 4, ' Sheng '); INSERT into ' data ' (' dates ', ' id', ' result ') VALUES (' 2015110101', 5, ' Sheng '); INSERT into ' data ' (' dates ', ' id ', ' result ') VALUES (' 2015109101 ', 6, ' negative '); INSERT into ' data ' (' dates ', ' id', ' result ') VALUES (' 2015109101', 7, ' Sheng '); INSERT into ' data ' (' dates ', ' id ', ' result ') VALUES (' 2015110101 ', 8, ' negative ');
Sort
select @rownum:[email protected]+1 AS rownum,id,dates from`data`,(SELECT @rownum:=0) r ORDER BY dates;
Results
Conditional query
SELECT rownum,idfrom (select @rownum:[email protected]+1 AS rownum,id,dates from `data`,(SELECT @rownum:=0) r ORDER BY dates)b WHERE id =2;
Results
Written in the last words
Get you a better way to implement RowNum () in MySQL, Welcome to enlighten me.
Mysql Implementation Rownum () after sorting according to the criteria to obtain the rank