如何选择MySQL表中按列分组的倒数第二行？

Question

结构是：

CREATE TABLE current
(
   id BIGINT NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(id),
   symbol VARCHAR(5),
   UNIQUE (id), INDEX (symbol)
) ENGINE MyISAM;

编号	符号
1	A
2	B
3	C
4	C
5	B
6	A
7	C
8	C
9	A
10	B

我正在使用以下内容

SELECT * 
FROM current
WHERE id
IN 
(
    SELECT MAX(id)
    FROM current
    GROUP BY symbol
)

如何返回表中的最后一条记录。

id	symbol
8	C
9	A
10	B

我该如何以类似的方式返回倒数第二个结果？

我知道我需要使用

ORDER BY id DESC LIMIT 1,1

我知道一些地方，但我的foo不是很强。

我想要返回：

编号	符号
5	B
6	A
7	C

- weirdbeard

---

也许你需要获取前两个结果，然后再使用 LIMIT 1 来获取前两个结果中的第一个。当然，你需要考虑排序的因素。我不知道数据集是什么，但我认为这对你的逻辑会有所帮助。 - Gui

发布样本数据和期望结果以澄清您的需求。 - forpas

能否举个例子详细说明一下？MySQL不是我的母语。 - weirdbeard

Answer 1

对于 MySql 8.0 之前的版本，可以在 WHERE 子句中使用子查询来过滤每个符号的最大 id，然后进行聚合：

SELECT MAX(id) id, symbol
FROM current
WHERE id NOT IN (SELECT MAX(id) FROM current GROUP BY symbol)
GROUP BY symbol
ORDER BY id;

请查看演示。

- forpas

---

这似乎是最快的方法，但如果我要向表中添加另一列（比如TIMESTAMP），那么id和symbol列是正确的，但TIMESTAMP看起来是来自第一行。 - weirdbeard

@weirdbeard，你不能在聚合查询中添加列。即使它可以运行（大多数数据库甚至不会接受它），列值也将是任意的。如果你想要其他列，你可以像这样做：https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ad27e2f7b8dc363fd039dc59be24b1b1 - forpas

Answer 2

SELECT * 
FROM current
WHERE id IN (
  SELECT DISTINCT T.id FROM current AS T
  WHERE id=(
    SELECT id FROM current
    WHERE symbol=T.symbol
    ORDER BY id DESC LIMIT 1,1
  )
)

- Rick Dou

---

感谢您的回复，但这些仅返回表格中倒数第二行，而不是按“符号”分组的所有行。 - weirdbeard

@weirdbeard，我已经编辑了我的代码。它能正常工作吗？ - Rick Dou

似乎已经成功了，但花了5分钟才完成。这个表有几百万条记录。 - weirdbeard

Answer 3

如果你的MySql可以使用ROW_NUMBER（MySql 8），那么这很容易。
只需将其按降序排序，然后取第二个即可。

WITH CTE AS (
    SELECT *
    , ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY id DESC) AS symbol_rn
    FROM current
) 
SELECT id, symbol
FROM CTE
WHERE symbol_rn = 2
ORDER BY id;

在MySql 7.5中，您可以简单地使用符号进行自连接，并进行分组。
然后，倒数第二个将具有1个更高的ID。

SELECT c1.id, c1.symbol
FROM current c1
LEFT JOIN current c2
  ON c2.symbol = c1.symbol
 AND c2.id >= c1.id
GROUP BY c1.id, c1.symbol
HAVING COUNT(c2.id) = 2
ORDER BY c1.id;

编号	符号
5	B
6	A
7	C

db<>fiddle 这里

在符号上建立索引会极大地提高性能。

- LukStorms

---

您的第一個解決方案（MySql 8）按預期返回結果並到目前為止是最佳答案。我在symbol上有索引，但查詢仍需要大約11秒才能運行。 - weirdbeard

有趣，不过我不确定你如何加速它。顺便问一下，第二个解决方案有多快？ - LukStorms

完成后我会通知你的 ;-) - weirdbeard

Answer 4

你可以尝试这个；

SELECT * 
FROM current
WHERE id
IN (SELECT MAX(id)
FROM current
GROUP BY symbol)
ORDER BY id DESC LIMIT 1,3

limit 1,3 表示：获取最后 3 个结果，但不包括最后一个结果。您可以更改数字。

- Feyza

---

这不会返回原帖作者期望的结果：https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=abc0096a8e8da7d53486469e36c51fc7 - forpas

我认为因为我们使用了group by，所以在链接示例中给出了那个结果。将id值为7的数据符号更改为D，然后再试一次。 - Feyza

不，那是因为你的答案是错误的。 - forpas

这取决于你从哪个角度看这个问题。 - Feyza

不，这取决于理解需求和了解代码的功能。如果您发现代码产生的结果与预期不符，则说明存在问题。 - forpas

我的代码正在按照我的要求执行。当我为这个问题编写代码时，这个问题并不是这样的。无论如何，谢谢！ - Feyza