如何“添加”当查询包含聚合函数时查询结果的列？

Question

我有一个名为“出勤”的表，用于记录学生在课程中的出勤时间。该表有 4 列，即“id”、“course_id”、“attendance_time”和“student_name”。此表中的几条记录的示例如下：

23    100   2010/1/1 10:00:00    Tom

24     100  2010/1/1 10:20:00    鲍勃

25    187   2010/1/2 0 8 :01:01    Lisa

.....

我想创建每门课程的最新出勤时间的摘要。我在下面创建了一个查询：

SELECT course_id, max(attendance_time) FROM attendance GROUP BY course_id

结果将类似于

100    1/1/2010 10:20:00

187    1/2/2010 08:01:01

Now ，我想做的就是将“id”列添加到上面的结果中。怎么做呢？

我不能只是将命令更改为这样的命令，

SELECT id, course_id, max(attendance_time) FROM attendance GROUP BY id, course_id

因为它会返回所有记录，就好像未使用聚合函数一样。请帮我。

Answer 1

这是一个典型的“每组最伟大”、“每组最伟大”或“按组最大”查询，几乎每天都会出现在 Stack Overflow 上。您可以在 Stack Overflow 中搜索这些术语，以找到如何使用不同数据库解决此问题的许多不同示例。解决该问题的一种方法如下：

SELECT
    T2.course_id,
    T2.attendance_time
    T2.id
FROM (
    SELECT
        course_id,
        MAX(attendance_time) AS attendance_time
    FROM attendance
    GROUP BY course_id
) T1
JOIN attendance T2
ON T1.course_id = T2.course_id
AND T1.attendance_time = T2.attendance_time

请注意，如果有多行具有相同的 attend_time，则理论上该查询可以为每个 course_id 返回多行。如果这不可能发生，那么您无需担心这个问题。如果这是一个潜在的问题，那么您可以通过在 course_id、attachment_time 上添加额外的分组并选择最小或最大 ID 来解决此问题。

Answer 2

对于 SQL Server 2008 及以后的版本，我喜欢使用通用表表达式向查询添加聚合列：

WITH AttendanceTimes (course_id, maxTime)
AS 
(
    SELECT
        course_id,
        MAX(attendance_time)
    FROM attendance
    GROUP BY course_id
)

SELECT
    a.course_id,
    t.maxTime,
    a.id
FROM attendance a
INNER JOIN AttendanceTimes t
ON a.course_id = t.course_id

Answer 3

您需要额外的列做什么？它已经有一个课程 ID，用于标识数据。查询的合成 ID 是无用的，因为它不引用任何内容。如果您想从单个课程的查询结果中获取最大值，那么您可以添加如下条件：

SELECT course_id, max(attendance_time) FROM attends GROUP BY course_id **WHERE course_id = your_id_here**;

如果您的意思是该列应命名为“id”，则可以在查询中为其指定别名：

SELECT course_id **AS id**, max(attendance_time) FROM attend GROUP BY course_id; >

您可以根据查询创建视图，以便轻松访问聚合数据：

CREATE VIEW max_course_times AS SELECT course_id AS id, max(attendance_time) FROM attendGROUP BY course_id;

SELECT * FROM max_course_times ;