mysql - 同时选择倒数第二个mysql值

我正在尝试从表中选择最大里程和第二大里程。

到目前为止，我的查询是：

SELECT oil.empid, oil.mileage, users.name, oil.date, MAX(oil.mileage)
FROM oil, users
WHERE oil.empid = users.empid
GROUP BY oil.empid
ORDER BY oil.mileage

但这会选择输入的第一个里程和最大

我该如何更改以获得第二大里程？

最佳答案

尝试这个：
如果U仅需要倒数第二个里程，则使用以下命令：

SELECT oil.empid, oil.mileage, users.name, oil.date, MAX(oil.mileage)
FROM oil, users
WHERE oil.empid = users.empid
GROUP BY oil.empid
ORDER BY oil.mileage desc
limit 1, 1

如果您同时需要最大值和倒数第二个，请使用以下命令：

SELECT oil.empid, oil.mileage, users.name, oil.date, MAX(oil.mileage)
FROM oil, users
WHERE oil.empid = users.empid
GROUP BY oil.empid
ORDER BY oil.mileage desc
limit 2