在子查询中选择最大值 [英] Select max value in subquery

问题描述

我有这两个表:

学生:

|                name |                   email |
|---------------------|-------------------------|
|    Arturo     Vidal |     [e-mail] |
|   Bastian   Quezada |          [e-mail] |
|    Javier     Jeria |           [e-mail] |
| Sebastian    Piñera | [e-mail] |
| Sebastian  Gallardo |        [e-mail] |

类:

| classId |                   email |  signUpDate |
|---------|-------------------------|-------------|
|       1 |        [e-mail] |  2018-01-01 |
|       1 |           [e-mail] |  2019-10-01 |
|       1 |          [e-mail] |  2018-07-01 |
|       2 |        [e-mail] |  2018-05-04 |
|       2 |          [e-mail] |  2018-01-01 |
|       3 |          [e-mail] |  2018-12-05 |
|       3 |        [e-mail] |  2018-02-01 |
|       4 |     [e-mail] |  2018-03-01 |
|       5 | [e-mail] |  2018-03-01 |

我想显示为每个 classId 注册的最后一个学生的姓名.这意味着，我应该为 classId 1 获取一个名称，为 classId 2 获取一个名称，等等.我首先收到邮件(之后知道学生的姓名)的解决方案是:

I want to show the name the last student that signed up for each classId. That means, I should get a name for classId 1, one for classId 2, etc. My solution for firstly getting the mails (to know the student's name after) is this:

select classId, email, max(signUpDate)
from Class
group by classId

它打印最大日期，这没问题，但它也会为每个日期打印错误的邮件:

it prints the max date, which is ok, but it also prints the wrong mails for each date:

| ClassId |                   email | max(signUpDate) |
|---------|-------------------------|-----------------|
|       1 |        [e-mail] |      2019-10-01 |
|       2 |        [e-mail] |      2018-05-04 |
|       3 |          [e-mail] |      2018-12-05 |
|       4 |     [e-mail] |      2018-03-01 |
|       5 | [e-mail] |      2018-03-01 |

这是完全错误的().因此，当我尝试加入获取名称的值时，我得到的值不正确.

which is completely wrong (). Therefore, when I try to join the the values for getting the names, I get incorrect values.

换句话说，我不明白为什么行会混淆.是否有任何解决方案可以为每个 ClassId 的 max(signUpDate) 获取正确的电子邮件?

In other words, I don't understand why are the rows mixing up. Is there any solution for getting correct emails for the max(signUpDate) for each ClassId?

感谢您的时间

推荐答案

我已经创建了测试数据小提琴，并做了一个简单易懂的查询来获取所需的数据，即:

i have created the test data fiddle and made an easy and understandable query to fetch the required data, i.e:

SELECT DISTINCT classId,
                std.name,
                Class.email,
                signUpDate
FROM CLASS
INNER JOIN Student std ON std.email = Class.email
WHERE signUpDate IN
    (SELECT max(signUpDate)
     FROM CLASS
     GROUP BY classId)

此处的Sql Fiddle

这篇关于在子查询中选择最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！