分组依据：如果我不想对列执行聚合函数怎么办？

I have a table, Students, with the following columns:

我有一张桌子，学生，有以下几栏：

________________________________________________
| id   |  name  |  class  |  date_registrered  | 
------------------------------------------------

I want to select one row for every unique class, and only the row with the largest value in date_registrered, i.e. I want to select the latest registrered Student for every class, including all the data for that one.

我想为每个唯一的类选择一行，并且只在date_registrered中选择具有最大值的行，即我想为每个类选择最新的注册学生，包括该类的所有数据。

I tried:

我试过了：

SELECT id, name, class, MAX(date_registrered)
FROM Students
GROUP BY class;

I get the following error:

我收到以下错误：

Column 'Students.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

列'Students.id'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

This question on SO adresses a simplified version of this issue. However, the example is for 2 columns only.

关于SO的这个问题解决了这个问题的简化版本。但是，该示例仅适用于2列。

I only want to group by class, and I only want to perform an aggregate function on date_registrered. I also want to display all the other columns for the row with the max date_registrered for every class.

我只想按类分组，我只想在date_registrered上执行聚合函数。我还想显示行的所有其他列，每个类都有max date_registrered。

Do you know how to fix it?

你知道怎么解决吗？

2 个解决方案

#1

The error message explains your issue very well, you can't perform an aggregation on one column, and not use the rest in the GROUP BY. In this case, you'll want to use something like ROW_NUMBER:

错误消息很好地解释了您的问题，您不能在一列上执行聚合，也不能在GROUP BY中使用其余的。在这种情况下，您将需要使用类似ROW_NUMBER的内容：

WITH CTE AS
(
    SELECT  id,
            name,
            class,
            date_registered,
            RN = ROW_NUMBER() OVER(PARTITION BY class ORDER BY date_registrered DESC)
    FROM students
)
SELECT  id,
        name,
        class,
        date_registered
FROM CTE
WHERE RN = 1;

#2

use ROW_NUMBER()

使用ROW_NUMBER（）

SELECT *
FROM ( SELECT id, name, class, date_registrered
              ROW_NUMBER() OVER (partition by class ORDER BY date_registrered DESC) rn
       FROM Students
     ) T
WHERE T.rn = 1

#1