【Oracle】浅析 用SQL求第K大问题
发布日期:2021-06-28 20:45:47
浏览次数:2
分类:技术文章
本文共 2105 字,大约阅读时间需要 7 分钟。
一,问题提出
问题:已知学生成绩表su,包含id,score两个字段,现需要取出成绩第二高的记录。
建表语句:-- oracleCREATE TABLE sc ( id NUMBER, score NUMBER ) -- mysqlCREATE TABLE `sc` ( `id` INT(11) DEFAULT NULL, `score` INT(11) DEFAULT NULL) ENGINE=INNODB DEFAULT CHARSET=gbk`sc`
数据插入:
--oracleINSERT INTO sc VALUES ('1', '43');INSERT INTO sc VALUES ('2', '77');INSERT INTO sc VALUES ('3', '86');INSERT INTO sc VALUES ('4', '66');INSERT INTO sc VALUES ('5', '25');INSERT INTO sc VALUES ('6', '46');INSERT INTO sc VALUES ('7', '77');--mysqlINSERT INTO `sc` VALUES ('1', '43');INSERT INTO `sc` VALUES ('2', '77');INSERT INTO `sc` VALUES ('3', '86');INSERT INTO `sc` VALUES ('4', '66');INSERT INTO `sc` VALUES ('5', '25');INSERT INTO `sc` VALUES ('6', '46');INSERT INTO `sc` VALUES ('7', '77');
二,求解方法
1,在Oracle中
这种问题,对于Oracle来说是小菜一碟,使用排名的分析函数就可以解决,但是要注意考虑有多个第K大的情况。 排名相关的分析函数有三个row_number、rank、dense_rank。这三个函数的使用情况并不相同,简单说下,详细用法请自度娘。
1,row_number:返回连续的排序,无论值是否相等。 2,rank:具有相等值得行排序相同,序数值随后跳跃。 3,dense_rank:具有相等值得行排序相同,序号是连续的。 举个例子: row_number : 对于相同的77,采用连续的序号。id score rn3 86 17 77 22 77 34 66 46 46 51 43 65 25 7
rank:对于相同的77,采用相同的序号2,但是66的序号并不是3,而是4。这是因为rank的序号是跳跃,第二个77的序号其实是3的,也就是序号跳了一位。
id score rn3 86 17 77 22 77 24 66 46 46 51 43 65 25 7
dense_rank: 对于相同的77,采用相同的序号2,但是序号不会因为相同数据而进行跳跃,所以66的序号是3。
id score rn3 86 17 77 22 77 24 66 36 46 41 43 55 25 6
针对于考虑重复值的第K大问题,应该使用dense_rank,如果采用rank,则序号就会有跳跃,结果不正确。
完整sql代码:select t.id,t.scorefrom (select ID, score, DENSE_RANK() over(order by score desc ) rnfrom sc) twhere t.rn=2 --第二大,所以取序号为2的
2,在MySQL中
对于MySQL,如果MySQL的版本<8.0,是使用不了Oracle中的三个排序分析函数的。所以就要有一个通用的方法来解决这种类似的问题,毕竟每种数据库所包含的函数是不同的,函数不一定通用,但是原始的SQL语法应该是一致的。
在MySQL中的方法中,最先想到的应该是类似以下这种写法(网上最多的):SELECT id, MAX(score) score FROM sc WHERE score < (SELECT MAX(score) FROM sc )
看上去很简洁,但是有两个问题:
1,查询出来的id,其实并不是我们所想的那样是最大score这条记录对应的id。大家可以动手运行下,我最开始也是这么认为。 2,对于用重复情况时,这条语句没法全部查询出来。3,通用的语句
那么有没有针对两个数据库,并且都满足要求的语句呢?方法应该有许多,以下语句大家可以参考下:
SELECT *FROM scWHERE score = (SELECTt1.scoreFROM sc t1JOIN sc t2 ON t1.score <= t2.score GROUP BY t1.scoreHAVING COUNT(DISTINCT t2.score) = 2) -- 以取第二大的为例子
如果有更好的方法,评论留言!!
转载地址:https://blog.csdn.net/yangjjuan/article/details/104905052 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
初次前来,多多关照!
[***.217.46.12]2024年04月03日 16时18分23秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
Jenkins资料整理
2019-04-29
ArrayList源码常用方法注意点
2019-04-29
MySQL资料整理
2019-04-29
Redis常用文章整理
2019-04-29
RocketMQ资料整理
2019-04-29
慢sql统计
2019-04-29
基于webRTC的1V1在线视频聊天(网页版DEMO)
2019-04-29
Disconf数据安全保护设计方案
2019-04-29
HttpClient获取302重定向的新网址方法
2019-04-29
Java 函数优雅之道【大厂规范】
2019-04-29
第三方接口调用规范
2019-04-29
java中调用js函数的方法
2019-04-29
可落地的云游戏解决方案
2019-04-29
Http协议原理分析
2019-04-29
HTTP协议工作原理、工作过程
2019-04-29
抖音视频批量去水印,抖音视频批量解析下载方法 - 2020年6月最新有效
2019-04-29
linux下redis安装遇到的问题及解决办法
2019-04-29
历史数据解决方案
2019-04-29
【基础+实战】JVM原理及优化系列之一:JVM体系结构
2019-04-29
【基础+实战】JVM原理及优化系列之二:JVM内存管理
2019-04-29