php - 查询中的 MethodID 重复

Question

我有一个使用子查询的查询，我似乎无法弄清楚为什么它告诉我我有重复的 methodID。该查询应该获取数据和排序依据和分组依据，以仅显示给定学生ID的最新单个结果，其中可能有多个具有不同时间戳但相同的学生ID的结果

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 students s
           ON s.studentID = r.studentID
         LEFT 
         JOIN staffaccounts a
           ON r.staffID = a.staffID
        WHERE 31 IN (newSubMethod,oldSubMethod) 
          AND date(timestamp) = CURRENT_DATE
     ) a
  JOIN 
     ( SELECT students.studentID
            , 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 students s
           ON s.studentID = r.studentID
         LEFT 
         JOIN staffaccounts a
           ON r.staffID = a.staffID
        WHERE 31 IN (newSubMethod,oldSubMethod) 
          AND date(timestamp) = CURRENT_DATE
    ) b
   ON b.studentID = a.studentID
  AND b.timestamp = a.timestamp;

关于这怎么可能的任何想法？

标签： phpmysqlgreatest-n-per-group