Mysql 实现 Rownum() 排序后根据条件获取名次-mysql教程-PHP中文网

首页

数据库

mysql教程

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:50 PM

mysql实现排序条件获取

初始化表结构 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="language-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="language-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="language-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>