MySQL SQL 显示 GROUP BY 中的最新记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10445162/
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
SQL Show most recent record in GROUP BY?
提问by A.B. User
I have a table that looks like this:
我有一张看起来像这样的表：
id | SubjectCode | Grade | DateApproved | StudentId
1 SUB123 1.25 1/4/2012 2012-12345
2 SUB123 2.00 1/5/2012 2012-12345
3 SUB123 3.00 1/5/2012 2012-98765
I'm trying to GROUP BY SubjectCode but i'd like it to display the most recent DateApproved so it will look like:
我正在尝试 GROUP BY SubjectCode 但我希望它显示最新的 DateApproved 所以它看起来像：
id | SubjectCode | Grade | DateApproved | StudentId
2 SUB123 2.00 1/5/2012 2012-12345
3 SUB123 3.00 1/5/2012 2012-98765
I'm a little bit lost on how to do it?
我有点不知道该怎么做？
EDIT:
编辑：
Ok guys now im on my real PC, sorry for the poorly constructed question.
好的，伙计们，我现在在我真正的 PC 上，对于结构不佳的问题感到抱歉。
Here's what I'm actually trying to do:
这是我真正想做的事情：
SELECT GD.GradebookDetailId, G.SubjectCode, G.Description, G.UnitsAcademic, G.UnitsNonAcademic,
GD.Grade, GD.Remarks, G.FacultyName, STR_TO_DATE(G.DateApproved, '%m/%d/%Y %h:%i:%s') AS 'DateAproved'
FROM gradebookdetail GD INNER JOIN gradebook G ON GD.GradebookId=G.GradebookId
WHERE G.DateApproved IS NOT NULL AND G.GradebookType='final' AND StudentIdNumber='2012-12345'
GROUP BY <?????>
ORDER BY G.SubjectCode ASC
Basically, I only want to display the most recent"DateApprove" of a "SubjectCode", so I don't get multiple entries.
基本上，我只想显示“SubjectCode”的最新“DateApprove”，所以我没有得到多个条目。
回答by Michael Buen
Start with this:
从这个开始：
select StudentId, max(DateApproved)
from tbl
group by StudentId
Then integrate that to main query:
然后将其集成到主查询中：
select *
from tbl
where (StudentId, DateApproved) in
(
select StudentId, max(DateApproved)
from tbl
group by StudentId
)
You can also use this:
你也可以使用这个：
select *
from tbl
join (select StudentId, max(DateApproved) as DateApproved
from tbl
group by StudentId)
using (StudentId, DateApproved)
But I prefer tuple testing, it's way neater
但我更喜欢元组测试，它更整洁
Live test: http://www.sqlfiddle.com/#!2/771b8/5
回答by Jaimal Chohan
SELECT t2.*
FROM temp t2
INNER JOIN(
SELECT MAX(DateApproved) as MaxDate, StudentId
FROM temp
GROUP BY StudentId
) t1 ON t1.MaxDate = t2.DateApproved and t1.StudentId = t2.StudentId
回答by erikkallen
SELECT *
FROM TheTable a
WHERE NOT EXISTS(SELECT *
FROM TheTable b
WHERE b.StudentCode = a.StudentCode AND b.DateApproved > a.DateApproved)
