一,问题提出
问题:已知学生成绩表su,包含id,score两个字段,现需要取出成绩第二高的记录。
建表语句:
-- oracle
CREATE TABLE sc (
id NUMBER,
score NUMBER
)
-- mysql
CREATE TABLE `sc` (
`id` INT(11) DEFAULT NULL,
`score` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=gbk
`sc`
数据插入:
--oracle
INSERT 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');
--mysql
INSERT 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 rn
3 86 1
7 77 2
2 77 3
4 66 4
6 46 5
1 43 6
5 25 7
rank:对于相同的77,采用相同的序号2,但是66的序号并不是3,而是4。这是因为rank的序号是跳跃,第二个77的序号其实是3的,也就是序号跳了一位。
id score rn
3 86 1
7 77 2
2 77 2
4 66 4
6 46 5
1 43 6
5 25 7
dense_rank: 对于相同的77,采用相同的序号2,但是序号不会因为相同数据而进行跳跃,所以66的序号是3。
id score rn
3 86 1
7 77 2
2 77 2
4 66 3
6 46 4
1 43 5
5 25 6
针对于考虑重复值的第K大问题,应该使用dense_rank,如果采用rank,则序号就会有跳跃,结果不正确。
完整sql代码:
select
t.id,
t.score
from (
select
ID,
score,
DENSE_RANK() over(order by score desc ) rn
from sc
) t
where 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
sc
WHERE score =
(SELECT
t1.score
FROM sc t1
JOIN sc t2 ON t1.score <= t2.score
GROUP BY t1.score
HAVING COUNT(DISTINCT t2.score) = 2) -- 以取第二大的为例子
如果有更好的方法,评论留言!!