查找第二大值的最简单的 SQL 查询是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32100/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the simplest SQL Query to find the second largest value?
提问by Niyaz
What is the simplest SQL query to find the second largest integer value in a specific column?
在特定列中查找第二大整数值的最简单 SQL 查询是什么？
There are maybe duplicate values in the column.
该列中可能存在重复值。
回答by Matt Rogish
SELECT MAX( col )
FROM table
WHERE col < ( SELECT MAX( col )
FROM table )
回答by Vinoy
SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);
回答by Keith
In T-Sql there are two ways:
在 T-Sql 中有两种方式：
--filter out the max
select max( col )
from [table]
where col < (
select max( col )
from [table] )
--sort top two then bottom one
select top 1 col
from (
select top 2 col
from [table]
order by col) topTwo
order by col desc
In Microsoft SQL the first way is twice as fast as the second, even if the column in question is clustered.
在 Microsoft SQL 中，第一种方法的速度是第二种方法的两倍，即使所讨论的列是群集的。
This is because the sort operation is relatively slow compared to the table or index scan that the maxaggregation uses.
这是因为与max聚合使用的表或索引扫描相比，排序操作相对较慢。
Alternatively, in Microsoft SQL 2005 and above you can use the ROW_NUMBER()function:
或者，在 Microsoft SQL 2005 及更高版本中，您可以使用该ROW_NUMBER()函数：
select col
from (
select ROW_NUMBER() over (order by col asc) as 'rowNum', col
from [table] ) withRowNum
where rowNum = 2
回答by Joel Coehoorn
I see both some SQL Server specific and some MySQL specific solutions here, so you might want to clarify which database you need. Though if I had to guess I'd say SQL Server since this is trivial in MySQL.
我在这里看到了一些特定于 SQL Server 的解决方案和一些特定于 MySQL 的解决方案，因此您可能想说明您需要哪个数据库。虽然如果我不得不猜测我会说 SQL Server 因为这在 MySQL 中是微不足道的。
I also see some solutions that won't work because they fail to take into account the possibility for duplicates, so be careful which ones you accept. Finally, I see a few that will work but that will make two complete scans of the table. You want to make sure the 2nd scan is only looking at 2 values.
我也看到一些解决方案不起作用，因为它们没有考虑到重复的可能性，所以要小心你接受哪些。最后，我看到了一些可行的方法，但它们会对表格进行两次完整的扫描。您想确保第二次扫描仅查看 2 个值。
SQL Server (pre-2012):
SQL Server（2012 年之前）：
SELECT MIN([column]) AS [column]
FROM (
SELECT TOP 2 [column]
FROM [Table]
GROUP BY [column]
ORDER BY [column] DESC
) a
MySQL:
MySQL：
SELECT `column`
FROM `table`
GROUP BY `column`
ORDER BY `column` DESC
LIMIT 1,1
Update:
更新：
SQL Server 2012 now supports a much cleaner (and standard) OFFSET/FETCH syntax:
SQL Server 2012 现在支持更清晰（和标准）的 OFFSET/FETCH 语法：
SELECT TOP 2 [column]
FROM [Table]
GROUP BY [column]
ORDER BY [column] DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;
回答by dguaraglia
I suppose you can do something like:
我想你可以这样做：
SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT 1 OFFSET 1
or
或者
SELECT * FROM Table ORDER BY NumericalColumn DESC LIMIT (1, 1)
depending on your database server. Hint: SQL Server doesn't do LIMIT.
取决于您的数据库服务器。提示：SQL Server 不做 LIMIT。
回答by user1796141
you can find the second largest value of column by using the following query
您可以使用以下查询找到列的第二大值
SELECT *
FROM TableName a
WHERE
2 = (SELECT count(DISTINCT(b.ColumnName))
FROM TableName b WHERE
a.ColumnName <= b.ColumnName);
you can find more details on the following link
您可以在以下链接中找到更多详细信息
http://www.abhishekbpatel.com/2012/12/how-to-get-nth-maximum-and-minimun.html
http://www.abhishekbpatel.com/2012/12/how-to-get-nth-maximum-and-minimun.html
回答by Magnar
The easiest would be to get the second value from this result set in the application:
最简单的方法是从应用程序中的这个结果集中获取第二个值：
SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2
But if you must select the second value using SQL, how about:
但是，如果您必须使用 SQL 选择第二个值，那么如何：
SELECT MIN(value) FROM (SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2) AS t
回答by petcy
A very simple query to find the second largest value
一个非常简单的查询来查找第二大值
SELECT `Column` FROM `Table` ORDER BY `Column` DESC LIMIT 1,1;
回答by Justin Jose
MSSQL
MSSQL
SELECT *
FROM [Users]
order by UserId desc OFFSET 1 ROW
FETCH NEXT 1 ROW ONLY;
MySQL
MySQL
SELECT *
FROM Users
order by UserId desc LIMIT 1 OFFSET 1
No need of sub queries ... just skip one row and select second rows after order by descending
不需要子查询......只需跳过一行并按降序选择第二行
回答by Nijish.
This is very simple code, you can try this :-
这是非常简单的代码，你可以试试这个：-
ex : Table name = test
例如：表名=测试
salary
1000
1500
1450
7500
MSSQL Code to get 2nd largest value
MSSQL 代码获得第二大值
select salary from test order by salary desc offset 1 rows fetch next 1 rows only;
here 'offset 1 rows' means 2nd row of table and 'fetch next 1 rows only' is for show only that 1 row. if you dont use 'fetch next 1 rows only' then it shows all the rows from the second row.
这里“偏移 1 行”表示表的第 2 行，“仅取下 1 行”仅用于显示该 1 行。如果您不使用“仅获取下 1 行”，则它会显示第二行中的所有行。
