MySQL的rank函数实现_MySQL
程序员文章站
2022-06-15 21:27:03
...
Mysql 的rank 函数如何实现 - 大鸟的学习乐园 - BlogJava
2.插入模拟数据
| a | b |
+------+------+
| 1 | 10 |
| 1 | 15 |
| 1 | 20 |
| 1 | 25 |
| 2 | 20 |
| 2 | 22 |
| 2 | 33 |
| 2 | 45 |
+------+------+
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
2.插入模拟数据
INSERT INTO `sam` VALUES (1,10),(1,15),(1,20),(1,25),(2,20),(2,22),(2,33),(2,45);+------+------+
| 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
4.结果:
+------+------+--------+------+
| a | b | rownum | rank |
+------+------+--------+------+
| 1 | 25 | 1 | 1 |
| 1 | 20 | 2 | 2 |
| 2 | 45 | 5 | 1 |
| 2 | 33 | 6 | 2 |
+------+------+--------+------+
4 rows in set (0.00 sec)注:
@x 为一变量,
X:=Y 将Y值赋给X
上一篇: 前端网页源码相关教程整理
下一篇: php form表单相关有关问题