Home > Database > Mysql Tutorial > body text

MySQL的rank函数实现_MySQL

WBOY
Release: 2016-06-01 13:09:53
Original
1932 people have browsed it

Mysql 的rank 函数如何实现 - 大鸟的学习乐园 - BlogJava 

http://www.blogjava.net/dunkbird/archive/2011/01/28/343718.html

表特征:

mysql> select * from test;

+------+------+
| a    | b    |
+------+------+
|    1 |   20 |
|    1 |   21 |
|    1 |   24 |
|    2 |   20 |
|    2 |   32 |
|    2 |   14 |
+------+------+
6 rows in set (0.00 sec)

现在,我们以a分组,查询b列最大的2个值。 这条sql要怎么写了?


1.创建表

Create Table: CREATE TABLE `sam` (  `a` int(11) DEFAULT NULL,  `b` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8
Copy after login

2.插入模拟数据

INSERT INTO `sam` VALUES (1,10),(1,15),(1,20),(1,25),(2,20),(2,22),(2,33),(2,45);
Copy after login
+------+------+
| a    | b    |
+------+------+
|    1 |   10 | 
|    1 |   15 | 
|    1 |   20 | 
|    1 |   25 | 
|    2 |   20 | 
|    2 |   22 | 
|    2 |   33 | 
|    2 |   45 | 
+------+------+

3.SQL实现

select a,b,rownum,rank from     (select ff.a,ff.b,@rownum:=@rownum+1 rownum,if(@pa=ff.a,@rank:=@rank+1,@rank:=1) as rank,@pa:=ff.a     FROM       (select a,b from sam group by a,b order by a asc,b desc) ff,(select @rank:=0,@rownum:=0,@pa=null) tt) result    having rank <br>4.结果:<br>+------+------+--------+------+<br>| a    | b    | rownum | rank |<br>+------+------+--------+------+<br>|    1 |   25 |      1 |    1 | <br>|    1 |   20 |      2 |    2 | <br>|    2 |   45 |      5 |    1 | <br>|    2 |   33 |      6 |    2 | <br>+------+------+--------+------+<br>4 rows in set (0.00 sec)<p>注:</p><p>@x 为一变量,<br>X:=Y 将Y值赋给X<br></p>
    
Copy after login
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template