如何用子查询基于MAX(TimeStampCreated)提取当前Active状态的Intake记录

要筛选当前状态为Active的客户，核心是先定位每个IntakeID对应的最新状态记录（即TimeStampCreated值最大的那条），再判断该记录的状态是否为Active。你之前的语句会取出所有曾有过Active状态的客户，无法满足“当前状态”的要求，以下是两种修正方案：

方案一：子查询匹配最新时间戳 #

SELECT
    CONCAT(d.FirstName,' ',d.MiddleName,' ',d.LastName) AS [Client Name],
    i.County, 
    i.SupervisingOfficer,
    i.SupervisionLevel AS [Supervision Level],
    ds.Status
FROM Defendants d 
JOIN Intake i ON d.ID = i.DefendantID
-- 关联筛选出每个IntakeID的最新状态记录
JOIN (
    SELECT 
        IntakeID, 
        Status,
        TimeStampCreated
    FROM DefendantStatusHistory
    WHERE (IntakeID, TimeStampCreated) IN (
        -- 先获取每个IntakeID的最新时间戳
        SELECT IntakeID, MAX(TimeStampCreated)
        FROM DefendantStatusHistory
        GROUP BY IntakeID
    )
) ds ON ds.IntakeID = i.ID
WHERE ds.Status = 'Active';

逻辑说明 #

• 内层子查询：按IntakeID分组，获取每个客户状态记录的最新时间戳。
• 中间子查询：通过时间戳匹配，找出每个IntakeID对应的最新状态记录。
• 最终关联主表，筛选出最新状态为Active的客户。

方案二：窗口函数（逻辑更直观） #

SELECT
    [Client Name],
    County, 
    SupervisingOfficer,
    [Supervision Level],
    Status
FROM (
    SELECT
        CONCAT(d.FirstName,' ',d.MiddleName,' ',d.LastName) AS [Client Name],
        i.County, 
        i.SupervisingOfficer,
        i.SupervisionLevel AS [Supervision Level],
        ds.Status,
        -- 按IntakeID分组，按时间戳倒序排序，最新记录标记为1
        ROW_NUMBER() OVER (PARTITION BY i.ID ORDER BY ds.TimeStampCreated DESC) AS rn
    FROM Defendants d 
    JOIN Intake i ON d.ID = i.DefendantID
    JOIN DefendantStatusHistory ds ON ds.IntakeID = i.ID
) t
WHERE rn = 1 AND Status = 'Active';

逻辑说明 #

• 内层查询用ROW_NUMBER()窗口函数，按IntakeID分组后，将每组内的记录按时间戳倒序排列，最新的状态记录会被标记为rn=1。
• 外层筛选rn=1且Status='Active'的记录，即为当前状态为Active的客户。

内容的提问来源于stack exchange，提问作者Haus