MySQL 选择日期最高的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6011052/
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
Selecting rows with the highest date
提问by London
I have some values which are repeating in my table, I want to select only those with the latest/highest date i.e :
我有一些值在我的表中重复，我只想选择那些具有最新/最高日期的值，即：
ID Type Name Value Date
-- ------- ----- ------- -------------
1 "FRUIT" "APPLE" "Imported" "2011-03-19 22:08:13"
5 "FRUIT" "LEMON" "Imported" "2011-03-19 22:00:44"
22 "FRUIT" "PEACH" "Imported" "2011-03-20 11:03:13"
31 "FRUIT" "MELON" "Imported" "2011-04-28 18:42:07"
44 "FRUIT" "PEACH" "Imported" "2011-04-12 11:06:11"
98 "FRUIT" "CHERRY" "Imported" "2011-03-19 22:46:04"
211 "FRUIT" "MELON" "Imported" "2011-03-19 22:25:24"
217 "VEG" "SPINACH""Imported" "2011-03-19 22:25:24"
I'd like to select these :
我想选择这些：
ID Type Name Value Date
-- ------- ----- ------- -------------
1 "FRUIT" "APPLE" "Imported" "2011-03-19 22:08:13"
5 "FRUIT" "LEMON" "Imported" "2011-03-19 22:00:44"
31 "FRUIT" "MELON" "Imported" "2011-04-28 18:42:07"
44 "FRUIT" "PEACH" "Imported" "2011-04-12 11:06:11"
98 "FRUIT" "CHERRY" "Imported" "2011-03-19 22:46:04"
This is simplified version of what I need, my table has about 20 columns so I want select *, if not I can select one by one.
这是我需要的简化版本，我的表有大约 20 列，所以我想要选择 *，如果没有，我可以一一选择。
So I want to select * rows of Type FRUIT but select only those with highest date. Thank you
所以我想选择 * 行的 Type FRUIT 但只选择日期最高的那些。谢谢
回答by IAmTimCorey
This should give you what you want:
这应该给你你想要的：
SELECT *
FROM Table
INNER JOIN
(SELECT Name, MAX(Date) as TopDate
FROM Table
WHERE Type = 'FRUIT'
GROUP BY Name) AS EachItem ON
EachItem.TopDate = Table.Date
AND EachItem.Name = Table.Name
Basically, it will find the latest date for each type of fruit and then display each fruit with the information for the row (joined on the date and fruit name). Make sure the Date field and Name field are both indexed.
基本上，它会找到每种水果的最新日期，然后显示每个水果和该行的信息（连接日期和水果名称）。确保日期字段和名称字段都已编入索引。
If you could assume that the item with the highest ID would also be the one with the highest date (typical but not necessarily true in all cases - it depends on your use case), you could do MAX(ID)instead of MAX(Date)and take advantage of just linking by that ID instead of linking by Date and Name.
如果您可以假设具有最高 ID 的项目也将是具有最高日期的项目（典型但不一定在所有情况下都是如此 - 这取决于您的用例），您可以MAX(ID)代替MAX(Date)并利用仅链接该 ID 而不是按日期和名称链接。
回答by Jason Small
If you are using mysql this will order the SELECT all the type "fruit" and will order the date from oldest to newest:
如果您使用的是 mysql，这将对 SELECT 所有类型“水果”进行排序，并将日期从最旧到最新排序：
SELECT * FROM tablename WHERE Type='fruit' ORDER by Date ASC
You could alo put a limit on it if needed (This would limit to the oldest 5):
如果需要，您也可以对其进行限制（这将限制为最旧的 5 个）：
SELECT * FROM tablename WHERE Type='fruit' ORDER by Date ASC LIMIT 5
