联合查询中MAX(RunDateTime)无法获取最新报表运行时间的问题

需求概述 #

需查询报表名称、最新运行时间（RunDateTime）、应用最后访问日期、激活状态等字段，报表存在三种启动方式：

• 通过应用启动器启动：会更新App表的LastAccessDate字段
• 通过报表启动器启动：需从ReportLog表提取MAX(RunDateTime)
• 第三种方式：仅更新ReportLog表，不更新App的LastAccessDate

现有问题 #

使用UNION ALL合并ReportSchedule和ReportLog表结果后取最新RunDateTime，但部分记录的MAX(RunDateTime)未返回真实最新日期。推测原因是同一报表在两个表中均有记录时，错误地将某一表的较新时间与另一表的旧数据合并，导致最终取到的是ReportScheduler中的旧LastRun，而非ReportLog里的最大值。

例如Report123单独执行SELECT MAX(RunDateTime) FROM ReportLog得到2024-09-18（与LastAccessDate一致），但当前查询返回的lastrun却是2021-09-17，且LastAccessDate字段是准确的。尝试用RunDateTime与LastAccessDate对比的方案，因存在LastAccessDate未更新的边缘情况，无法稳定生效。

现有SQL代码 #

DECLARE @ThresholdDate DATETIME;
SET @ThresholdDate = '2021-09-18';

SELECT
    reportinfo.ReportName,
    MAX(reportinfo.lastrun) AS lastrun,
    reportinfo.AppID,
    reportinfo.AppName,
    reportinfo.Active,
    reportinfo.LastAccessDate
FROM (
    SELECT
        ReportLog.ReportName,
        MAX(ReportLog.RunDateTime) AS lastrun,
        App.AppName,
        App.Active,
        App.AppID,
        App.LastAccessDate
    FROM ReportLog
    INNER JOIN App ON App.AppID = ReportLog.AppFk
    WHERE RIGHT(ReportLog.ReportName, 4) = '.rpt'
    GROUP BY ReportLog.ReportName, App.AppName, App.Active, App.AppID, App.LastAccessDate

    UNION ALL

    SELECT
        App.AppPath AS ReportName,
        MAX(ReportSchedule.lastrun) AS lastrun,
        App.AppName,
        App.Active,
        App.AppID,
        App.LastAccessDate
    FROM ReportSchedule
    INNER JOIN App ON App.AppID = ReportSchedule.AppFk
    GROUP BY App.AppPath, App.AppName, App.Active, App.AppID, App.LastAccessDate
) AS reportinfo
WHERE reportinfo.lastrun < @ThresholdDate
GROUP BY
    reportinfo.ReportName,
    reportinfo.AppID,
    reportinfo.AppName,
    reportinfo.Active,
    reportinfo.LastAccessDate
ORDER BY lastrun DESC;

返回结果示例 #

解决建议 #

1. 调整子查询逻辑：先合并原始数据再聚合 #

不要在子查询中分别对两个表做MAX聚合，而是先合并两个表的原始记录（关联App表后），再统一按报表名称和应用信息聚合取最大时间。这样能确保同一报表的所有来源时间都参与比较，取到真正的最大值。

修改后的代码：

DECLARE @ThresholdDate DATETIME;
SET @ThresholdDate = '2021-09-18';

SELECT
    reportinfo.ReportName,
    MAX(reportinfo.RunDateTime) AS lastrun,
    reportinfo.AppID,
    reportinfo.AppName,
    reportinfo.Active,
    reportinfo.LastAccessDate
FROM (
    -- 从ReportLog取单条运行记录
    SELECT
        ReportLog.ReportName,
        ReportLog.RunDateTime,
        App.AppName,
        App.Active,
        App.AppID,
        App.LastAccessDate
    FROM ReportLog
    INNER JOIN App ON App.AppID = ReportLog.AppFk
    WHERE RIGHT(ReportLog.ReportName, 4) = '.rpt'

    UNION ALL

    -- 从ReportSchedule取单条lastrun记录
    SELECT
        App.AppPath AS ReportName,
        ReportSchedule.lastrun AS RunDateTime,
        App.AppName,
        App.Active,
        App.AppID,
        App.LastAccessDate
    FROM ReportSchedule
    INNER JOIN App ON App.AppID = ReportSchedule.AppFk
) AS reportinfo
GROUP BY
    reportinfo.ReportName,
    reportinfo.AppID,
    reportinfo.AppName,
    reportinfo.Active,
    reportinfo.LastAccessDate
-- 先聚合再过滤，避免提前丢弃影响最大值的记录
HAVING MAX(reportinfo.RunDateTime) < @ThresholdDate
ORDER BY lastrun DESC;

2. 校验报表名称的一致性 #

检查App.AppPath作为报表名称是否与ReportLog.ReportName完全匹配（比如大小写、后缀、是否存在多余字符），如果存在不一致，会导致同一报表被拆分为多条记录，无法正确聚合最大值。可以统一格式，例如：

-- 统一转为小写并保留后缀
LOWER(ReportLog.ReportName) AS ReportName
-- 和
LOWER(App.AppPath) AS ReportName

3. 结合LastAccessDate处理边缘场景 #

针对应用启动器启动（更新LastAccessDate）和第三种仅更新ReportLog的场景，可以在聚合时额外对比LastAccessDate，取两者的最大值作为最终lastrun，示例：

SELECT
    reportinfo.ReportName,
    MAX(
        IIF(reportinfo.RunDateTime > reportinfo.LastAccessDate, 
            reportinfo.RunDateTime, 
            reportinfo.LastAccessDate
        )
    ) AS lastrun,
    reportinfo.AppID,
    reportinfo.AppName,
    reportinfo.Active,
    reportinfo.LastAccessDate
FROM (...) AS reportinfo
-- 后续聚合、过滤逻辑...

注：此方案需确认业务规则中LastAccessDate与RunDateTime的关联场景，避免引入错误数据。

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