Mysql 的rank 函数如何实现
表特征:
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 <=2
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)
一枚小产品的学习记录
在做sql练习题时了解到的新函数,做个简要的记录,若有不严谨的地方,请指正。
mysql 8.0以上版本才支持窗口函数
以下是个人总结
另外:over(partition by字段1 order by 字段2)中的partition by 字段1 是可以省略的但是order by 字段2 不可省略
详细演示见下文:
本文内使用到的建表、插入数据sql 可在我写的sql面试50题的第一篇文章内找到.
按各科成绩进行排序,并显示排名
#rank()over()的使用
#执行结果如下
#rank()over()的使用,不使用partition by的效果
#执行结果如下
#row_number()over()的使用
#执行结果如下
#dense_rank()over()的使用
#执行结果如下
#****与group by 共同使用
#对学生总成绩进行从大到小排序
#执行结果如下
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)