SQL-表中第二高的记录 [英] SQL - 2nd highest record in table

查看：162 发布时间：2020/11/20 20:27:10 sql greatest-n-per-group

本文介绍了SQL-表中第二高的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

SELECT MAX(Score)
FROM Students
WHERE Score < (SELECT MAX(Score) FROM Students);

上面的查询可以完美地工作，并获取得分第二高的记录，而下面提到的查询则不获取任何内容

the above query works perfectly and fetches the record that has 2nd highest score, whereas the query mentioned below does not fetch anything

SELECT *
FROM Students
WHERE Score < (SELECT MAX(Score) FROM Students);

学生"是我要从中获取该记录的所有详细信息的表，该记录在整个表中排名第二.

here Students is the table from which I want to fetch all the details of that record which has 2nd highest score in the entire table.

我希望执行第二个查询，在此先感谢您的帮助.

I want that 2nd query should get executed, thanks in advance for helping me out.

对于标准SQL，通常使用

With standard SQL, this is typically solved using window functions:

select *
from (
  select *, dense_rank() over (order by score desc) as rnk
  from students
) t
where rnk = 2;

以上是ANSI标准SQL，适用于所有现代DBMS.

The above is ANSI standard SQL and works on all modern DBMS.

这篇关于SQL-表中第二高的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！