如何在表格中找到第三高的值？ [英] How can i find the third highest value in a table?

问题描述

我希望SQL / PLSQL查询从我的表中找到字段的第三高值（EX：Salary）。

I want a SQL/PLSQL query to find the third highest value for a field (EX:Salary) from my table.

推荐答案

检查此链接



第n个最高值 [ ^ ]



第n个最高值 [ ^ ]

Check this link

nth highest value[^]

nth highest value[^]

你需要使用其中一个排名函数 [ ^ ]。



例如，您可以使用 ROW_NUMBER（）。试试这个：

You need to use one of the ranking functions[^].

For example, you can use ROW_NUMBER(). Try this:

DECLARE @myTable TABLE (aValue INT)

INSERT INTO @myTable (aValue)
VALUES(123)
INSERT INTO @myTable (aValue)
VALUES(456)
INSERT INTO @myTable (aValue)
VALUES(789)
INSERT INTO @myTable (aValue)
VALUES(234)
INSERT INTO @myTable (aValue)
VALUES(567)
INSERT INTO @myTable (aValue)
VALUES(345)
INSERT INTO @myTable (aValue)
VALUES(678)
INSERT INTO @myTable (aValue)
VALUES(999)

--view all records
SELECT ROW_NUMBER() OVER(ORDER BY aValue DESC) AS aPosition, aValue
FROM @myTable

结果：

Results:

1	999
2	789
3	678
4	567
5	456
6	345
7	234
8	123

--view only the third highest value
SELECT aValue As [ThirdHighestValue]
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY aValue DESC) AS aPosition, aValue
    FROM @myTable
) AS t1
WHERE t1.aPosition = 3

结果：678

Result: 678

试试

Try

SELECT a.Salary FROM MyTable a
WHERE 3 = (SELECT COUNT(DISTINCT (b.Salary)) FROM MyTable b WHERE a.Salary <= b.Salary)

这篇关于如何在表格中找到第三高的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！