在特定列中查找第二大整数值的最简单的SQL查询是什么？
列中可能有重复的值。
1
2
3
4
| SELECT MAX( col )
FROM TABLE
WHERE col < ( SELECT MAX( col )
FROM TABLE ) |
1
| SELECT MAX(col) FROM TABLE WHERE col NOT IN (SELECT MAX(col) FROM TABLE); |
在T-Sql中，有两种方法：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| --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 |
在Microsoft SQL中，即使所讨论的列是群集的，第一种方法的速度也是第二种方法的两倍。
这是因为与max聚合使用的表或索引扫描相比，排序操作相对较慢。
或者，在Microsoft SQL 2005及更高版本中，可以使用ROW_NUMBER()函数：
1
2
3
4
5
| SELECT col
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY col ASC) AS 'rowNum', col
FROM [TABLE] ) withRowNum
WHERE rowNum = 2 |
我在这里看到一些特定于SQL Server的解决方案和某些特定于MySQL的解决方案，因此您可能需要弄清所需的数据库。虽然如果我不得不猜测我会说SQL Server，因为这在MySQL中是微不足道的。
我还看到一些无法使用的解决方案，因为它们没有考虑重复的可能性，因此请小心接受哪些解决方案。最后，我看到一些可以使用的方法，但是它将对表进行两次完整的扫描。您要确保第二次扫描仅查看2个值。
SQL Server(2012年前)：
1
2
3
4
5
6
7
| SELECT MIN([COLUMN]) AS [COLUMN]
FROM (
SELECT TOP 2 [COLUMN]
FROM [TABLE]
GROUP BY [COLUMN]
ORDER BY [COLUMN] DESC
) a |
MySQL的：
1
2
3
4
5
| SELECT `column`
FROM `table`
GROUP BY `column`
ORDER BY `column` DESC
LIMIT 1,1 |
更新：
SQL Server 2012现在支持更简洁(和标准)的OFFSET / FETCH语法：
1
2
3
4
5
6
| SELECT TOP 2 [COLUMN]
FROM [TABLE]
GROUP BY [COLUMN]
ORDER BY [COLUMN] DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY; |
我想您可以执行以下操作：
1
| SELECT * FROM TABLE ORDER BY NumericalColumn DESC LIMIT 1 OFFSET 1 |
要么
1
| SELECT * FROM TABLE ORDER BY NumericalColumn DESC LIMIT (1, 1) |
取决于您的数据库服务器。提示：SQL Server不执行LIMIT。
您可以使用以下查询找到列的第二大值
1
2
3
4
5
6
| SELECT *
FROM TableName a
WHERE
2 = (SELECT COUNT(DISTINCT(b.ColumnName))
FROM TableName b WHERE
a.ColumnName <= b.ColumnName); |
您可以在以下链接上找到更多详细信息
http://www.abhishekbpatel.com/2012/12/how-to-get-nth-maximum-and-minimun.html
最简单的方法是从应用程序中的此结果集中获取第二个值：
1
| SELECT DISTINCT VALUE FROM TABLE ORDER BY VALUE DESC LIMIT 2 |
但是，如果必须使用SQL选择第二个值，该如何做：
1
| SELECT MIN(VALUE) FROM (SELECT DISTINCT VALUE FROM TABLE ORDER BY VALUE DESC LIMIT 2) AS t |
一个非常简单的查询来找到第二个最大值
1
| SELECT `Column` FROM `Table` ORDER BY `Column` DESC LIMIT 1,1; |
1
| SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee ) |
该查询将从结果中返回最高薪水-不包含总表中的最高薪水。
我知道的老问题，但这给了我一个更好的执行计划：
1
2
3
| SELECT TOP 1 LEAD(MAX (COLUMN)) OVER (ORDER BY COLUMN DESC)
FROM TABLE
GROUP BY COLUMN |
1
2
3
4
| SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Col_x DESC) AS ROW, Col_1
FROM table_1)AS table_new tn INNER JOIN table_1 t1
ON tn.col_1 = t1.col_1
WHERE ROW = 2 |
希望此帮助获得任何行的值.....
最简单
1
| SELECT sal FROM salary ORDER BY sal DESC LIMIT 1 offset 1 |
MSSQL
1
2
3
4
| SELECT *
FROM [Users]
ORDER BY UserId DESC OFFSET 1 ROW
FETCH NEXT 1 ROW ONLY; |
MySQL的
1
2
3
| SELECT *
FROM Users
ORDER BY UserId DESC LIMIT 1 OFFSET 1 |
无需子查询...只需跳过一行，然后按降序选择第二行
这是非常简单的代码，您可以尝试：-
例如：
表名称=测试
1
2
3
4
5
6
| salary
1000
1500
1450
7500 |
MSSQL代码获得第二大价值
1
| SELECT salary FROM test ORDER BY salary DESC offset 1 ROWS fetch NEXT 1 ROWS ONLY; |
这里"偏移1行"表示表的第二行，"仅获取下1行"仅用于显示该1行。如果您不使用"仅获取下1行"，则它将显示第二行中的所有行。
汤姆，相信当select max([COLUMN_NAME]) from [TABLE_NAME]部分返回的值不止一个时，这将失败。即数据集中有两个以上的值。
轻微修改您的查询即可-
1
2
| SELECT MAX([COLUMN_NAME]) FROM [TABLE_NAME] WHERE [COLUMN_NAME] **IN**
( SELECT MAX([COLUMN_NAME]) FROM [TABLE_NAME] ) |
1
2
| SELECT MAX(column_name) FROM TABLE_NAME
WHERE column_name NOT IN (SELECT MAX(column_name) FROM TABLE_NAME); |
不存在是排除column_name最大值的条件。
参考：程序员面试
1
2
3
4
5
6
7
8
| SELECT
*
FROM
TABLE
WHERE
COLUMN < (SELECT MAX(columnq) FROM TABLE)
ORDER BY
COLUMN DESC LIMIT 1 |
1
2
| SELECT MIN(sal) FROM emp WHERE sal IN
(SELECT TOP 2 (sal) FROM emp ORDER BY sal DESC) |
注意
sal是上校名称
emp是表名
1
| SELECT age FROM student GROUP BY id HAVING age<(SELECT MAX(age) FROM student)ORDER BY age LIMIT 1 |
这是最简单的方法：
1
2
3
4
5
6
7
| SELECT
COLUMN name
FROM
TABLE name
ORDER BY
COLUMN name DESC
LIMIT 1,1 |
1
2
| SELECT MAX(sal) FROM emp
WHERE sal NOT IN (SELECT top 3 sal FROM emp ORDER BY sal DESC ) |
这将返回emp表的第三高工资
1
2
3
4
5
| SELECT col_name
FROM (
SELECT dense_rank() OVER (ORDER BY col_name DESC) AS 'rank', col_name
FROM TABLE_NAME ) withrank
WHERE rank = 2 |
正如您提到的重复值。在这种情况下，您可以使用DISTINCT和GROUP BY找出第二高的值
这是一张桌子
salary
：
GROUP BY
1
2
3
4
| SELECT amount FROM salary
GROUP BY amount
ORDER BY amount DESC
LIMIT 1 , 1 |
DISTINCT
1
2
3
4
| SELECT DISTINCT amount
FROM salary
ORDER BY amount DESC
LIMIT 1 , 1 |
LIMIT的第一部分=起始索引
LIMIT的第二部分=多少值
1
2
| SELECT MAX(COL_NAME) FROM TABLE_NAME WHERE COL_NAME IN
(SELECT COL_NAME FROM TABLE_NAME WHERE COL_NAME < (SELECT MAX(COL_NAME) FROM TABLE_NAME)); |
子查询返回除最大值以外的所有值。
从返回的列表中选择最大值。
像这样吗我还没有测试过：
1
2
3
4
5
6
7
| SELECT top 1 x
FROM (
SELECT top 2 DISTINCT x
FROM y
ORDER BY x DESC
) z
ORDER BY x |
使用相关查询：
1
| SELECT * FROM x x1 WHERE 1 = (SELECT COUNT(*) FROM x WHERE x1.a < a) |
1
2
| SELECT * FROM emp e WHERE 3>=(SELECT COUNT(DISTINCT salary)
FROM emp WHERE s.salary<=salary) |
此查询选择最多三个薪水。如果两个emp获得相同的薪水，这不会影响查询。
这适用于MS SQL：
1
2
| SELECT MAX([COLUMN_NAME]) FROM [TABLE_NAME] WHERE [COLUMN_NAME] <
( SELECT MAX([COLUMN_NAME]) FROM [TABLE_NAME] ) |
1
2
3
4
| SELECT top 1 MyIntColumn FROM MyTable
WHERE
MyIntColumn <> (SELECT top 1 MyIntColumn FROM MyTable ORDER BY MyIntColumn DESC)
ORDER BY MyIntColumn DESC |
这是查找列第二大值的另一种方法，考虑表'Student'和列'Age'。然后查询是，
1
| SELECT top 1 Age FROM Student WHERE Age IN(SELECT DISTINCT top 2 Age FROM Student ORDER BY Age DESC) ORDER BY Age ASC |
请参阅如何在SQL数据库表中选择第n行？
Sybase SQL Anywhere支持：
1
| SELECT TOP 1 START AT 2 VALUE FROM TABLE ORDER BY VALUE |
查询以找到连续第二高的数字-
1
2
3
| SELECT Top 1 (salary) FROM XYZ
WHERE Salary NOT IN (SELECT DISTINCT TOP 1(salary) FROM XYZ ORDER BY Salary DESC)
ORDER BY Salary DESC |
通过将突出显示的Top 1更改为TOP 2，3或4，u可以分别找到第三高，第四高和第五高。
1
2
3
4
5
| SELECT * FROM EMP
WHERE salary=
(SELECT MAX(salary) FROM EMP
WHERE salary != (SELECT MAX(salary) FROM EMP)
); |
首先创建一个没有最高工资的虚拟表，然后从虚拟表中查询最大值
1
| SELECT MAX(salary) FROM (SELECT * FROM emp WHERE salary<> (SELECT MAX(salary) FROM emp)) temp |
尝试：
1
2
3
4
| SELECT a.* ,b.* FROM
(SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY fc_amount DESC) SrNo1, fc_amount AS amount1 FROM entry GROUP BY fc_amount) tbl WHERE tbl.SrNo1 = 2) a
,
(SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY fc_amount ASC) SrNo2, fc_amount AS amount2 FROM entry GROUP BY fc_amount) tbl WHERE tbl.SrNo2 =2) b |
1
| SELECT * FROM [TABLE] WHERE (COLUMN)=(SELECT MAX(COLUMN)FROM [TABLE] WHERE COLUMN < (SELECT MAX(COLUMN)FROM [TABLE])) |
使用此查询。
1
2
3
4
5
| SELECT MAX ( colname )
FROM Tablename
WHERE colname < (
SELECT MAX( colname )
FROM Tablename) |
1
| SELECT MAX(salary) AS SecondMax FROM test WHERE salary !=(SELECT MAX(salary) FROM test) |
1
2
3
| SELECT score
FROM TABLE
WHERE score = (SELECT MAX(score)-1 FROM TABLE) |
Microsoft SQL Server-使用两个TOP作为第N个最高值(别名子查询)。
解决第二高的问题：
1
2
3
| SELECT TOP 1 q.*
FROM (SELECT TOP 2 column_name FROM TABLE_NAME ORDER BY column_name DESC) AS q
ORDER BY column_name ASC; |
使用TOP两次，但是需要一个别名子查询。本质上，内部查询以降序获取最大的2个值，然后外部查询以升序翻转，以使第二高的值现在位于顶部。 SELECT语句返回此顶部。
要求解第n个最大值，请修改子查询的TOP值。例如：
1
2
3
| SELECT TOP 1 q.*
FROM (SELECT TOP 5 column_name FROM TABLE_NAME ORDER BY column_name DESC) AS q
ORDER BY column_name; |
将返回第5个最高值。
1
| SELECT extension FROM [dbo].[Employees] ORDER BY extension DESC offset 2 ROWS fetch NEXT 1 ROWS ONLY |
很简单。
distinct关键字也将处理重复项。
1
| SELECT DISTINCT SupplierID FROM [Products] ORDER BY SupplierID DESC LIMIT 1 offset 1 |
从SQL表中获取倒数第二行的最简单方法是使用ORDER BY ColumnName DESC并设置LIMIT 1,1。
尝试这个：
1
| SELECT * FROM `TableName` ORDER BY `ColumnName` DESC LIMIT 1,1 |
1
2
3
| SELECT * FROM `employee` WHERE employee_salary = (SELECT employee_salary
FROM`employee` GROUP BY employee_salary ORDER BY employee_salary DESC LIMIT
1,1) |
您可以使用以下查询找到第n个最大值。
1
2
| SELECT top 1 UnitPrice FROM (SELECT DISTINCT top n UnitPrice FROM
[ORDER Details] ORDER BY UnitPrice DESC) AS RESULT ORDER BY UnitPrice ASC |
在这里，n的值将是1(对于最高的数字)，2(对于第二高的数字)，3(对于第三高的数字)...
我们还可以如下使用order by和top 1元素：
1
2
3
| SELECT top 1 col_name FROM TABLE_NAME
WHERE col_name < (SELECT top 1 col_name FROM TABLE_NAME ORDER BY col_name DESC)
ORDER BY col_name DESC |
