加入群组中的 MAX 日期记录 [英] Joining to MAX date record in group

问题描述

Job
--------
Id
Description


JobStatus
----------
Id
JobId
StatusTypeId
Date

如何获取所有作业的当前 JobStatus?

How do I get the current JobStatus for all jobs?

有点像……

SELECT * FROM Job j
INNER JOIN ( /* Select rows with MAX(Date) grouped by JobId */ ) s
    ON j.Id = s.JobId

(我确定已经有很多类似的问题，但我找不到任何完全符合我需要的问题).

(I'm sure there are a bunch of similar questions already but I couldn't find anything which exactly does what I need).

推荐答案

在 SQL Server 2005+ 中:

SELECT  *
FROM    job j
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    jobstatus js
        WHERE   js.jobid = j.jobid
        ORDER BY
                js.date DESC
        ) js

在 SQL Server 2000 中:

SELECT  *
FROM    job j
LEFT JOIN
        jobstatus js
ON      js.id =
        (
        SELECT  TOP 1 id
        FROM    jobstatus jsi
        WHERE   jsi.jobid = j.jobid
        ORDER BY
                jsi.date DESC
        )

这些查询会正确处理 Date 上可能的重复项.

These queries handle possible duplicates on Date correctly.

这篇关于加入群组中的 MAX 日期记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！