MySQL 选择其中一列值不同的 3 个最近的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/924494/
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 the 3 most recent records where the values of one column are distinct
提问by atp
I have the following table:
我有下表：
id time text otheridentifier
-------------------------------------------
1 6 apple 4
2 7 orange 4
3 8 banana 3
4 9 pear 3
5 10 grape 2
What I want to do is select the 3 most recent records (by time desc), whose otheridentifiers are distinct. So in this case, the result would be id's: 5, 4, and 2.
我想要做的是选择 3 个最近的记录（按时间降序），它们的otheridentifiers 是不同的。所以在这种情况下，结果将是id's: 5、4 和 2。
id= 3 would be skipped because there's a more recent record with the same otheridentifierfield.
id= 3 将被跳过，因为存在具有相同otheridentifier字段的更新记录。
Here's what I tried to do:
这是我尝试做的：
SELECT * FROM `table` GROUP BY (`otheridentifier`) ORDER BY `time` DESC LIMIT 3
However, I end up getting rows of id= 5, 3, and 1instead of 5, 4, 2 as expected.
但是，我最终得到了id= 5, 3和1行，而不是预期的 5, 4, 2 行。
Can someone tell me why this query wouldn't return what I expected? I tried changing the ORDER BY to ASC but this simply rearranges the returned rows to 1, 3, 5.
有人能告诉我为什么这个查询不会返回我期望的吗？我尝试将 ORDER BY 更改为 ASC 但这只是将返回的行重新排列为 1、3、5。
采纳答案by chaos
It doesn't return what you expect because grouping happens before ordering, as reflected by the position of the clauses in the SQL statement. You're unfortunately going to have to get fancier to get the rows you want. Try this:
它不会返回您期望的内容，因为分组发生在排序之前，正如 SQL 语句中子句的位置所反映的那样。不幸的是，您将不得不变得更漂亮才能获得所需的行。尝试这个：
SELECT *
FROM `table`
WHERE `id` = (
SELECT `id`
FROM `table` as `alt`
WHERE `alt`.`otheridentifier` = `table`.`otheridentifier`
ORDER BY `time` DESC
LIMIT 1
)
ORDER BY `time` DESC
LIMIT 3
回答by Andomar
You could join the table on itself to filter the last entry per otheridentifier, and then take the top 3 rows of that:
您可以加入表格本身以过滤 per 的最后一个条目otheridentifier，然后取其中的前 3 行：
SELECT last.*
FROM `table` last
LEFT JOIN `table` prev
ON prev.`otheridentifier` = last.`otheridentifier`
AND prev.`time` < last.`time`
WHERE prev.`id` is null
ORDER BY last.`time` DESC
LIMIT 3
回答by 11101101b
I had a similar requirement, but I had more advanced selection criteria. Using some of the other answers I couldn't get exactly what I needed, but I found you can still do a GROUP BY after and ORDER BY like this:
我有类似的要求，但我有更高级的选择标准。使用其他一些答案我无法完全得到我需要的东西，但我发现你仍然可以像这样执行 GROUP BY after 和 ORDER BY：
SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t
GROUP BY t.otheridentifier
回答by php
You can use this query to get correct answer:
您可以使用此查询获得正确答案：
SELECT * FROM
(SELECT * FROM `table` order by time DESC)
t group by otheridentifier
回答by mechanical_meat
Andomar's answeris probably best in that it doesn't use a subquery.
Andomar 的答案可能是最好的，因为它不使用子查询。
An alternative approach:
另一种方法：
select *
from `table` t1
where t1.`time` in (
select max(s2.`time`)
from `table` t2
group by t2.otheridentifier
)
回答by Rytmis
SELECT * FROM table t1
WHERE t1.time =
(SELECT MAX(time) FROM table t2
WHERE t2.otheridentifier = t1.otheridentifier)
回答by mbo
what about
关于什么
SELECT *, max(time) FROM `table` group by otheridentifier
回答by user2450223
This also:
这也是：
SELECT * FROM
OrigTable T INNER JOIN
(
SELECT otheridentifier,max(time) AS duration
FROM T
GROUP BY otheridentifier) S
ON S.duration = T.time AND S.otheridentifier = T.otheridentifier.
