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

初始化表结构 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` ( `dat

初始化表结构

<code class="sql hljs "><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> <span class="hljs-string">`data`</span>;</span>
<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`data`</span> (
  <span class="hljs-string">`dates`</span> <span class="hljs-keyword">varchar</span>(<span class="hljs-number">255</span>) <span class="hljs-keyword">CHARACTER</span> <span class="hljs-keyword">SET</span> utf8 <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,
  <span class="hljs-string">`id`</span> <span class="hljs-keyword">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,
  <span class="hljs-string">`result`</span> <span class="hljs-keyword">varchar</span>(<span class="hljs-number">255</span>) <span class="hljs-keyword">CHARACTER</span> <span class="hljs-keyword">SET</span> utf8 <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>
);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`data`</span> (<span class="hljs-string">`dates`</span>, <span class="hljs-string">`id`</span>, <span class="hljs-string">`result`</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2015109101'</span>, <span class="hljs-number">1</span>, <span class="hljs-string">'胜'</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`data`</span> (<span class="hljs-string">`dates`</span>, <span class="hljs-string">`id`</span>, <span class="hljs-string">`result`</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2015110101'</span>, <span class="hljs-number">2</span>, <span class="hljs-string">'负'</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`data`</span> (<span class="hljs-string">`dates`</span>, <span class="hljs-string">`id`</span>, <span class="hljs-string">`result`</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2015109101'</span>, <span class="hljs-number">3</span>, <span class="hljs-string">'负'</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`data`</span> (<span class="hljs-string">`dates`</span>, <span class="hljs-string">`id`</span>, <span class="hljs-string">`result`</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2015109101'</span>, <span class="hljs-number">4</span>, <span class="hljs-string">'胜'</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`data`</span> (<span class="hljs-string">`dates`</span>, <span class="hljs-string">`id`</span>, <span class="hljs-string">`result`</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2015110101'</span>, <span class="hljs-number">5</span>, <span class="hljs-string">'胜'</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`data`</span> (<span class="hljs-string">`dates`</span>, <span class="hljs-string">`id`</span>, <span class="hljs-string">`result`</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2015109101'</span>, <span class="hljs-number">6</span>, <span class="hljs-string">'负'</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`data`</span> (<span class="hljs-string">`dates`</span>, <span class="hljs-string">`id`</span>, <span class="hljs-string">`result`</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2015109101'</span>, <span class="hljs-number">7</span>, <span class="hljs-string">'胜'</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-string">`data`</span> (<span class="hljs-string">`dates`</span>, <span class="hljs-string">`id`</span>, <span class="hljs-string">`result`</span>) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'2015110101'</span>, <span class="hljs-number">8</span>, <span class="hljs-string">'负'</span>);</span></code>

排序

<code class="sql hljs "><span class="hljs-operator"><span class="hljs-keyword">select</span> @rownum:=@rownum+<span class="hljs-number">1</span> <span class="hljs-keyword">AS</span> rownum,id,dates 
<span class="hljs-keyword">from</span>
<span class="hljs-string">`data`</span>,(<span class="hljs-keyword">SELECT</span> @rownum:=<span class="hljs-number">0</span>) r 
<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> dates;</span></code>

结果

条件查询

<code class="sql hljs "><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> rownum,id
<span class="hljs-keyword">from</span>
    (<span class="hljs-keyword">select</span> @rownum:=@rownum+<span class="hljs-number">1</span> <span class="hljs-keyword">AS</span> rownum,id,dates
     <span class="hljs-keyword">from</span>
    <span class="hljs-string">`data`</span>,(<span class="hljs-keyword">SELECT</span> @rownum:=<span class="hljs-number">0</span>) r 
    <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> dates)b 
    <span class="hljs-keyword">WHERE</span> id =<span class="hljs-number">2</span>;</span></code>

结果

写在最后的话

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