MySQL SELECT DISTINCT ORDER BY 问题

Question

首先，我正在处理 4 张桌子。

我有一个 classes 表，它与 sections 表之间存在 1->N 关系，而 sections 表也与 lessons 存在 1->N 关系强>表。

因此，从正确的角度来看：

• 类
  • 部分
    • 教训

最后一个表格是一个activityLog，当学生访问课程时，将使用以下内容进行记录：

ActivityLog Row ->; actorID（用户 ID）、classID、sectionID、lessonID

我想提取学生访问过的最后 5 个独特课程。我尝试同时使用 DISTINCT 和 GROUP BY 但没有成功。

每次都会返回相同的记录，而不是他们访问过的最新课程。

使用 GROUP BY

SELECT activityLog.actorID, activityLog.activityDate,
        strClasses.classID, strClasses.className,
        strSections.sectionID, strSections.sectionName,
        strLessons.lessonID, strLessons.lessonName

FROM activityLog            
LEFT JOIN strClasses ON strClasses.classID = activityLog.classID
LEFT JOIN strSections ON strSections.sectionID = activityLog.sectionID
LEFT JOIN strLessons ON strLessons.lessonID = activityLog.lessonID
WHERE activityLog.activityTypeID = 6 AND activityLog.actorID = 3
GROUP BY activityLog.lessonID
ORDER BY activityLog.activityDate DESC
LIMIT 5

使用 DISTINCT

SELECT DISTINCT activityLog.actorID,
        strClasses.classID, strClasses.className,
        strSections.sectionID, strSections.sectionName,
        strLessons.lessonID, strLessons.lessonName
FROM activityLog            
LEFT JOIN strClasses ON strClasses.classID = activityLog.classID
LEFT JOIN strSections ON strSections.sectionID = activityLog.sectionID
LEFT JOIN strLessons ON strLessons.lessonID = activityLog.lessonID
WHERE activityLog.activityTypeID = 6 AND activityLog.actorID = 3
ORDER BY activityLog.activityDate DESC
LIMIT 5

我无法弄清楚为什么没有显示最新记录。

Answer 1

尝试如下分组
GROUP BY ActivityLog.classID、activityLog.sectionID、activityLog.lessonID

我认为它会起作用，或者只是让我为这些创建脚本，我将创建该查询

Answer 2

好吧，我希望 ActivityLog 中必须有一个日期时间......所以试试这个：

 Select s.Name, c.ClassName
 From Students s
     left Join On Classes c
          On c.ClassId In 
               (Select Distinct ClassId From Classes
                Where (Select Count(Distinct ClassId) From Classes ic
                         Join ActivityLog l On l.UserId = s.UserId
                            And l.ClassId = c.ClassId                         
                       Where classId = c.ClassId
                          And activityDateTime > l.activityDateTime) 
                    < 5)

Answer 3

根据您的更改，这是否适合您？

SELECT activityLog.actorID, activityLog.activityDate,
        strClasses.classID, strClasses.className,
        strSections.sectionID, strSections.sectionName,
        strLessons.lessonID, strLessons.lessonName
FROM activityLog            
LEFT JOIN strClasses ON strClasses.classID = activityLog.classID
LEFT JOIN strSections ON strSections.sectionID = activityLog.sectionID
LEFT JOIN strLessons ON strLessons.lessonID = activityLog.lessonID
WHERE activityLog.activityTypeID = 6 AND activityLog.actorID = 3
AND activityLog.activityDate = (SELECT MAX(activityDate) FROM activityLog AS lookup WHERE lessonID = activityLog.lessonID)
ORDER BY activityLog.activityDate DESC
LIMIT 5

根据您的描述，我不确定您为什么使用LEFT JOIN，但为了以防万一，我保留了它。