仅获取最新的时间戳条目

iezvtpos 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(363)

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

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

mysql greatest-n-per-group

来源：https://stackoverflow.com/questions/52610770/getting-the-latest-timestamp-entry-only-mysql

1条答案

按热度按时间

57hvy0tb1#

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;

赞(0）回复(0）举报 2021-06-20

我来回答

仅获取最新的时间戳条目

1条答案

相关问题

热门标签

最新问答