SQL为嵌套选择创建别名列？

Say I have a Person table and a Courses table. In the Person table I have the column PersonName. In the Courses table, let's say I have CourseTitle,PersonName and CourseDifficulty. CourseDifficulty is 1-4 (4 being the hardest). How do I return a list of people from Person and for each person have a column that shows the most difficult class they're taking by CourseTitle.

假设我有一个Person表和一个Courses表。在Person表中,我有PersonName列。在Courses表中,假设我有CourseTitle,PersonName和CourseDifficulty。 CourseDifficulty是1-4(4是最难的)。如何从Person返回一个人员列表,每个人都有一个列,显示他们正在通过CourseTitle进行的最困难的课程。

As far as I know, I'd get the CourseTitle of the most difficult class Brett is taking by doing the following:

据我所知,我将通过以下方式获得Brett最困难的课程的课程标题:

SELECT CourseTitle 
FROM Courses 
WHERE PersonName = 'Brett' 
  AND CourseDifficulty = (SELECT MAX(CourseDifficulty) 
                          FROM Courses 
                          WHERE PersonName='Brett')

But how do I run that for each person in the Person table? I want the results to be something like

但是如何为Person表中的每个人运行它?我希望结果是这样的

Brett-SQL For Dummies 4
Tim-Quantum Mechanics
Jane-Thermodynamics 2

Sorry for the noobness. Thanks in advance for the help!

抱歉,这是一个noobness。先谢谢您的帮助!

2 个解决方案

#1

you can use the following

你可以使用以下

SELECT p.name ,p.address, c.courseTitle ,c.courseDifficulty FROM (
        SELECT personName, courseTitle, MAX(courseDifficulty) AS courseDifficulty
        FROM course
        GROUP BY personName 
) AS c RIGHT JOIN person AS p ON p.name = c.personName

here am assuming personName is a unique. Otherwise you can use unique id over here instead on person name and add this field in select statement.

这里假设personName是唯一的。否则,您可以在此处使用唯一ID而不是人名,并在select语句中添加此字段。

#2

Try this:

SELECT c.CourseTitle, c.PersonName, c.CourseDifficulty
  FROM Courses c
 WHERE c.CourseDifficulty=(SELECT MAX(c2.CourseDifficulty) FROM Courses c2 WHERE c2.PersonName=c.PersonName)

#1