MySQL SELECT DISTINCT ORDER BY問題 - MySQL SELECT DISTINCT ORDER BY problem -开发者知识库

MySQL SELECT DISTINCT ORDER BY問題 - MySQL SELECT DISTINCT ORDER BY problem -开发者知识库,第1张

To begin with I have 4 tables I am dealing with.

首先,我正在處理4個表。

I have a classes table that is a 1->N relationship with a sections table which also has a 1->N relationship with a lessons table.

我有一個類表,它是一個1-> N關系的一個sections表,它與一個課程表有一個1-> N的關系。

So to put it in perpective:

所以要把它放在一起:

  • Classes
    • Sections
      • Lessons
  • 課程部分課程

The last table is an activityLog, when the student accesses a lesson this is recorded using the following:

最后一個表是一個activityLog,當學生訪問課程時,使用以下內容記錄:

ActivityLog Row -> actorID (user ID), classID, sectionID, lessonID

ActivityLog Row - > actorID(用戶ID),classID,sectionID,lessonID

I want to pull out the last 5 unique lessons the student has visited. I tried using both DISTINCT and GROUP BY without success.

我想拉出學生訪問過的最后5個獨特的課程。我嘗試使用DISTINCT和GROUP BY都沒有成功。

The same records are being returned each time, not the latest classes that they have visited.

每次都返回相同的記錄,而不是他們訪問過的最新類。

Using 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

Using 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

I cannot figure out why the latest records are not being displayed.

我無法弄清楚為什么沒有顯示最新的記錄。

3 个解决方案

#1


2  

Based on your change, how does this suit you?

根據您的變化,這對您有何幫助?

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

Based on your description, I'm not sure why you're using LEFT JOIN, but I've left it in just in case.

根據你的描述,我不確定你為什么使用LEFT JOIN,但為了以防萬一,我把它留下了。

最佳答案:

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复