SQL显示GROUP BY中的最新记录？ [英] SQL Show most recent record in GROUP BY?

问题描述

我有一张如下所示的表格：

  id | SubjectCode |等级| DateApproved | StudentId 
 
 1 SUB123 1.25 1/4/2012 2012-12345 
 
 2 SUB123 2.00 1/5/2012 2012-12345 
 
 3 SUB123 3.00 2012年1月5日2012-98765 
  

我想要GROUP BY SubjectCode，但我想它显示最新的DateApproved，因此它看起来像：

  id | SubjectCode |等级| DateApproved | StudentId 
 
 2 SUB123 2.00 1/5/2012 2012-12345 
 
 3 SUB123 3.00 1/5/2012 2012-98765 
  
 
我有点失落了，怎么做？
 
 
 
编辑： p> 
 
 
好吧，现在即时通讯在我的真实个人电脑上，对于构建不好的问题感到抱歉。 
 
 
 
以下是我实际要做的事： 
 
 
  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'和StudentIdNumber ='2012-12345'
 
 GROUP BY< ?????> 
 ORDER BY G.SubjectCode ASC 
  
基本上，我只想显示 SubjectCode的最新DateApprove，所以我没有获得多个条目。   开始用这个：
 
 
  select StudentId，max（DateApproved）
 from tbl 
 by StudentId 
  
然后将其集成到主要查询中： 
 
 
  select * 
 from tbl 
其中（StudentId，DateApproved）在
 
（
 select StudentId，max（DateApproved）
 from tbl 
 group by StudentId 
）
  
您也可以使用：
  select * 
 from tbl 
 join（select StudentId，max（DateApproved）as DateApproved 
从tbl 
 group by StudentId）
使用（StudentId，DateApproved）
  
但我更喜欢元组测试，它的方式是neater  
 
 
 
现场测试： http://www.sqlfiddle.com/#!2/771b8/5  
 
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:
  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. 

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.
解决方案
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

                        
这篇关于SQL显示GROUP BY中的最新记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！