【Oracle】浅析 用SQL求第K大问题

一，问题提出

问题：已知学生成绩表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)   -- 以取第二大的为例子

如果有更好的方法，评论留言！！