仅从LEFT JOIN返回最新结果 [英] Returning only latest result from LEFT JOIN

问题描述

我正在查询两个表(students2014和notes2014)中的数据，以便返回学生列表以及每个学生的笔记.为此，我使用以下select语句:

I am querying data from two tables (students2014 and notes2014) in order to return a list of students along with notes on each student. To do this I am using the following select statement:

SELECT * 
FROM students2014 
LEFT JOIN notes2014 
ON students2014.Student = notes2014.NoteStudent 
WHERE students2014.Consultant='$Consultant' 
ORDER BY students2014.LastName

这成功地给了我一个列表，但是音符不止一个的学生出现两次，例如:

This successfully gives me a list, however students with more than one note appear twice, for e.g:

• 学生a-注释
• 学生a-注释
• 学生b-注释
• 学生c-注意

等...

我只希望为每个学生显示最新的笔记，因此只提供一次每个学生的列表.

I only want the latest note to appear for each student, thus delivering a list of each student only once.

希望有道理吗?

推荐答案

您需要使用子查询将studends表联接起来.这样的事情应该起作用:

You need to join the studends table with a sub query. Something like this should work:

SELECT * 
FROM `students2014`
LEFT JOIN (
    SELECT `note`, `NoteStudent`
    FROM `notes2014`
    HAVING `NoteID` = MAX(`NoteID`)
    GROUP BY `NoteStudent`
) `notes`
ON `students2014`.`Student` = `notes`.`NoteStudent`
WHERE `students2014`.`Consultant`='$Consultant' 
ORDER BY `students2014`.`LastName`

这篇关于仅从LEFT JOIN返回最新结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！