MySQL SELECT 列中具有第二大值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18280138/
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
SELECT rows with the second highest value in a column
提问by John Obertan
Let's say I have a table similar to the following:
假设我有一个类似于以下内容的表：
Item Description Time
----- ----------- -----
ItemA1 descript 08-16-2013 00:00:00
ItemA2 descript 08-16-2013 00:00:00
ItemA3 descript 08-16-2013 00:00:00
.
.
ItemAN descript 08-16-2013 00:00:00
ItemB1 descript 08-13-2013 00:00:00
ItemB2 descript 08-13-2013 00:00:00
ItemB3 descript 08-13-2013 00:00:00
.
.
ItemBN descript 08-13-2013 00:00:00
.
.
.
ItemX1 descript 01-13-2012 00:00:00
ItemX2 descript 01-13-2012 00:00:00
ItemX3 descript 01-13-2012 00:00:00
.
.
ItemXN descript 01-13-2012 00:00:00
Groups of items are added periodically. When a group of items is added they are all added with the same "Time" field. "Time" essentially serves as a unique index for that item group.
定期添加项目组。添加一组项目时，它们都添加了相同的“时间”字段。“时间”本质上用作该项目组的唯一索引。
I want to SELECT the group of items that have the second highest time. In this example my query should pull the "B" items. I know I can do max(time) to SELECT the "A" items, but I don't know how I would do second last.
我想选择具有第二高时间的项目组。在这个例子中，我的查询应该拉出“B”项。我知道我可以用 max( time) 来选择“A”项，但我不知道倒数第二个我会怎么做。
My "Time" columns are stored as TIMESTAMP if that means anything.
如果这意味着什么，我的“时间”列将存储为 TIMESTAMP。
回答by DarkAjax
You can try something like:
您可以尝试以下操作：
SELECT MAX(Time)
FROM yourTable
WHERE Time < (SELECT MAX(Time) FROM yourTable)
回答by spencer7593
One approach:
一种方法：
SELECT t.*
FROM mytable t
JOIN ( SELECT l.time
FROM mytable l
GROUP BY l.time
ORDER BY l.time DESC
LIMIT 1,1
) m
ON m.time = t.time
This uses an inline view (assigned an alias of m) to return the second "greatest" time value. The GROUP BY gets us a distinct list, the ORDER BY DESC puts the latest first, and the "trick" is the LIMIT, which returns the second row. LIMIT(m,n) = (skip first m rows, return next n rows)
这使用内联视图（分配了别名 m）来返回第二个“最大”时间值。GROUP BY 为我们提供了一个不同的列表，ORDER BY DESC 将最新的放在最前面，“技巧”是 LIMIT，它返回第二行。LIMIT(m,n) =（跳过前 m 行，返回下 n 行）
With that time value, we can join back to the original table, to get all rows that have a matching time value.
有了那个时间值，我们可以连接回原始表，以获取具有匹配时间值的所有行。
Performance will be enhanced with an index with leading column of time. (I think MySQL should be able to avoid a "Using filesort" operation, and get the result from the inline view query fairly quickly.)
性能将通过前导列为 的索引得到增强time。（我认为 MySQL 应该能够避免“使用文件排序”操作，并且相当快地从内联视图查询中获得结果。）
But, including a predicate in the inline view query, if you "know" that the second latest time will never be more than a certain number of days old, won't hurt performance:
但是，在内联视图查询中包含谓词，如果您“知道”第二个最新时间永远不会超过特定天数，则不会影响性能：
WHERE l.time > NOW() + INTERVAL -30 DAYS
But with that added, then the query won't return the "second latest" group if it's timeis more than 30 days ago.
但是添加后，如果查询time超过 30 天前，则查询将不会返回“第二个最新”组。
The SELECT MAX(time) WHERE time < ( SELECT MAX(time)approach to get the second latest (the approach given in other answers) might be faster, especially if there is no index with leading column of time, but performance would best be gauged by actual testing. The index with leading column of time will speed up the MAX() approach as well.)
该SELECT MAX(time) WHERE time < ( SELECT MAX(time)方法以获得第二最新的（在其他的答案中给出的方法）可能会更快，尤其是如果有与领先的列上没有索引time，但性能最好由实际测试来衡量。具有领先时间列的索引也将加速 MAX() 方法。）
The query I provided can be easily extended to get the 4th latest, 42nd latest, etc, by changing the LIMITclause... LIMIT(3,1), LIMIT(41,1), etc.
所提供的查询，我可以很容易地扩展，以获得最新的第4，第42届最新的等，通过改变LIMIT条款等LIMIT(3,1)，LIMIT(41,1)等。
回答by Aycan Ya??t
This should give you second biggest time:
这应该给你第二大的时间：
SELECT time FROM table GROUP BY time ORDER BY time DESC LIMIT 1,1
回答by Chaman Saini
Get second, third, fourth......Nth highest value using following query:
使用以下查询获取第二、第三、第四......第 N 个最高值：
SELECT MIN(value) from yourTable WHERE value IN( SELECT TOP N value FROM yourTable ORDER BY value DESC)
Replace N by you number i.e. N=2 for second highest value, N=3 for third highest value and so on. So for second highest value use:
用您的数字替换 N，即 N=2 表示第二高的值，N=3 表示第三高的值，依此类推。因此，对于第二高的价值使用：
SELECT MIN(value) from yourTable WHERE value IN( SELECT TOP 2 value FROM yourTable ORDER BY value DESC)
回答by Declan_K
SELECT T1.ITEM
FROM YOURTABLE T1
WHERE T1.TIME = ( SELECT MAX(T2.TIME)
FROM YOURTABLE T2
WHERE T2.TIME < ( SELECT MAX(T3.TIME)
FROM YOURTABLE T3
)
)
回答by bielawski
Something really straightforward like this should work
像这样非常简单的东西应该可以工作
select * from my-table where time =
(Select top 1 time
from (select top 2 time from my-table order by time desc)
order by time asc)
回答by Bernd Schuhmacher
Here is another solution which I think should work for your problem:
这是我认为应该适用于您的问题的另一种解决方案：
CREATE TEMPORARY TABLE xHighest AS (SELECT DISTINCT Time FROM `my-table` ORDER BY Time DESC LIMIT 1,1);
SELECT * FROM `my-table` JOIN xHighest ON (my-table.Time = xHighest.Time);
You can choose if second, third, ... highest valueshould be used by changing the first parameter of LIMIT.
您可以通过更改 LIMIT 的第一个参数来选择是否应使用第二、第三、...最高值。
回答by Muhammad Abbas
MYSQL: limit base solution
MYSQL：限制基础解决方案
Example: records 1, 2, 3, 4, 5.
LIMIT 2,1 means it will return 3rd highest number, as LIMIT 1,1 return 2nd highest number and so on
示例：记录 1、2、3、4、5。
LIMIT 2,1 表示将返回第三大数字，如 LIMIT 1,1 返回第二大数字，依此类推
SELECT rc.rc_officer_idFROM recovery_complain_officer rc ORDER BY rc.rc_officer_idDESC LIMIT 2,1
选择 rc。rc_officer_idFROM recovery_complain_officer rc ORDER BY rc。rc_officer_id降速限制 2,1
回答by seaders
Leftfield-ish answer, but this allows you to select n-1 ordered values from a single table as you want (credit to the https://stackoverflow.com/a/35234692/618320answer by @Uueerdo), with nearlyno extra cost.
Leftfield-ish 答案，但这允许您根据需要从单个表中选择 n-1 个有序值（归功于@Uueerdo的https://stackoverflow.com/a/35234692/618320答案），几乎没有额外的成本。
You can use GROUP_CONCAT(DISTINCT...), followed by some SUBSTRING(...),
您可以使用GROUP_CONCAT(DISTINCT...)，后跟一些SUBSTRING(...)，
SELECT
SUBSTRING_INDEX(groupTime, ',', 1) AS time1,
SUBSTRING_INDEX(SUBSTRING_INDEX(groupTime, ',', 2), ',', -1) AS time2,
SUBSTRING_INDEX(SUBSTRING_INDEX(groupTime, ',', 3), ',', -1) AS time3
FROM (
SELECT GROUP_CONCAT(DISTINCT Time ORDER BY Time DESC) groupTime FROM mytable) t
The inner query would give you a single-row result back like "08-16-2013 00:00:00,08-13-2013 00:00:00,01-13-2012 00:00:00", and splitting that string up the way we are doing, gives you a table result like,
内部查询会给你一个单行结果，比如“08-16-2013 00:00:00,08-13-2013 00:00:00,01-13-2012 00:00:00”，然后拆分以我们正在做的方式串起来，给你一个表格结果，比如，
time1 time2 time3
--------- ------------ ------------
08-16-2013 00:00:00 08-13-2013 00:00:00 01-13-2012 00:00:00
回答by Naveen Kumar
Maybe TOP and START AT can help here. I have used this in Sybase SQL.
也许 TOP 和 START AT 可以在这里提供帮助。我在 Sybase SQL 中使用过它。
SELECT TOP 1 START AT 2 * FROM Table_Name ORDER BY CREATEDDATETIME DESC;
SELECT TOP 1 START AT 2 * FROM Table_Name ORDER BY CREATEDDATETIME DESC;
