仅获取最新的时间戳条目 MYSQL

debugcn 发表于 Dev

罗伯特·迪基

我有一个为报告提取数据的查询。它目前提取所有记录，但我被要求这样做，以便它只从每个 sID 的 changeReport 表中提取最新条目

    select *, old.methodName as oldName, new.methodName as newName, students.firstName as fName, students.lastName as lName
from changeReport 
left join methodLookup as old on (old.methodID = changeReport.oldMethod) 
left join methodLookup as new on (new.methodID = changeReport.newMethod) 
join students on (students.studentID = changeReport.studentID) 
left join staffaccounts on (changeReport.staffID = staffaccounts.staffID) 
where 31 IN (newSubMethod,oldSubMethod) AND date(timestamp) = CURRENT_DATE

如何提取相同的报告，但仅显示每个 sID 的最新时间戳？每个 sID 每天可能有 1-10 个条目……但我只想提取最新的条目。

我已经尝试参考其他几篇最大的每组帖子，但似乎无法找到针对此特定问题的解决方案。

服务器类型：MariaDB 服务器版本：5.5.60-MariaDB - MariaDB 服务器协议版本：10

草莓

SELECT a.* 
  FROM 
     ( SELECT *
            , o.methodName oldName
            , n.methodName newName
            , s.firstName fName
            , s.lastName lName 
         FROM changeReport r
         LEFT 
         JOIN methodLookup o
           ON o.methodID = r.oldMethod
         LEFT 
         JOIN methodLookup n
           ON n.methodID = r.newMethod
         JOIN s s
           ON s.sID = r.studentID
         LEFT 
         JOIN staffaccounts a
           ON r.staffID = a.staffID
        WHERE 31 IN (newSubMethodm,oldSubMethod) 
          AND DATE(timestamp) = CURRENT_DATE
     ) a
  JOIN 
     ( SELECT s.sid
            , MAX(timestamp) timestamp
         FROM changeReport r
         LEFT 
         JOIN methodLookup o
           ON o.methodID = r.oldMethod
         LEFT 
         JOIN methodLookup n
           ON n.methodID = r.newMethod
         JOIN s s
           ON s.sID = r.studentID
         LEFT 
         JOIN staffaccounts a
           ON r.staffID = a.staffID
        WHERE 31 IN (newSubMethodm,oldSubMethod) 
          AND DATE(timestamp) = CURRENT_DATE
        GROUP
           BY s.sid 
    ) b
   ON b.sid = a.sid
  AND b.timestamp = a.timestamp;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。