SQL关联查询未达预期：需按time字段取最新数据且alarmtime<time

首先，你的核心需求是查询以time字段为最新标准，且满足alarmtime < time的数据，但原SQL存在几个关键问题导致结果不符合预期：

• 完全没有添加alarmtime < time的筛选条件，这是需求的核心约束
• 关联result_video_v2时，仅匹配了cameraId，没有结合a.time做时间范围过滤，导致拿到的entryTime（即alarmtime）可能不符合"小于对应time"的要求
• 分组逻辑不严谨：当你GROUP BY a.db33, b.cameraName时，c.entryTime没有被聚合处理，在部分数据库（如MySQL非严格SQL模式）下会随机返回一条值，无法保证是符合条件的最新alarmtime

修正后的SQL方案 #

我调整了SQL逻辑，确保满足alarmtime < time的约束，同时保证每个DB33对应的time是最新值，alarmtime是该time之前的最新值：

SELECT 
    c.latest_alarmtime AS alarmtime,
    a.latest_time AS time,
    a.DB33,
    b.cameraName
FROM (
    -- 第一步：获取每个DB33的最新time（保留你原有的取最近1000条再聚合的逻辑，避免全表扫描）
    SELECT 
        t.DB33,
        MAX(t.Time) AS latest_time,
        t.Stream,
        t.Coil,
        t.`View`
    FROM (
        SELECT DB33, Time, Stream, Coil, `View` 
        FROM running_check 
        ORDER BY id DESC LIMIT 1000
    ) AS t
    GROUP BY t.DB33, t.Stream, t.Coil, t.`View`
) AS a
LEFT JOIN monitor_link_info AS b 
    ON a.DB33 = b.cameraID
LEFT JOIN (
    -- 第二步：获取每个cameraID对应的、小于对应latest_time的最新alarmtime
    SELECT 
        cameraID,
        MAX(entryTime) AS latest_alarmtime
    FROM result_video_v2
    WHERE entryTime < (
        SELECT MAX(t.Time) 
        FROM (
            SELECT Time FROM running_check ORDER BY id DESC LIMIT 1000
        ) AS t
    ) -- 先过滤全局范围内小于最新time的记录，再按cameraID聚合
    GROUP BY cameraID
) AS c 
    ON a.DB33 = c.cameraID
-- 第三步：添加核心约束，确保alarmtime < time
WHERE c.latest_alarmtime < a.latest_time OR c.latest_alarmtime IS NULL
ORDER BY a.latest_time DESC, c.latest_alarmtime DESC;

关键调整说明 #

• 新增了WHERE c.latest_alarmtime < a.latest_time的核心筛选条件，严格满足需求
• 对result_video_v2的查询做了聚合处理，确保每个cameraID拿到的是符合时间范围的最新alarmtime
• 保留了你原有的LIMIT 1000逻辑来优化查询性能，避免全表扫描（如果你的数据量很大，这个逻辑是合理的）
• 修正了分组字段，确保符合SQL标准（避免非聚合字段出现在SELECT中但不在GROUP BY里的问题）

如果你的数据库支持窗口函数（如MySQL 8.0+、PostgreSQL等），还可以用更简洁的窗口函数写法来获取每个DB33的最新time和对应符合条件的alarmtime：

WITH latest_running AS (
    SELECT 
        DB33,
        Time AS latest_time,
        Stream,
        Coil,
        `View`,
        ROW_NUMBER() OVER (PARTITION BY DB33 ORDER BY Time DESC) AS rn
    FROM running_check
    ORDER BY id DESC LIMIT 1000
),
latest_alarm AS (
    SELECT 
        cameraID,
        entryTime AS latest_alarmtime,
        ROW_NUMBER() OVER (PARTITION BY cameraID ORDER BY entryTime DESC) AS rn
    FROM result_video_v2
    WHERE entryTime < (SELECT MAX(latest_time) FROM latest_running)
)
SELECT 
    la.latest_alarmtime AS alarmtime,
    lr.latest_time AS time,
    lr.DB33,
    mli.cameraName
FROM latest_running lr
LEFT JOIN monitor_link_info mli ON lr.DB33 = mli.cameraID
LEFT JOIN latest_alarm la ON lr.DB33 = la.cameraID AND la.rn = 1
WHERE lr.rn = 1 AND (la.latest_alarmtime < lr.latest_time OR la.latest_alarmtime IS NULL)
ORDER BY lr.latest_time DESC, la.latest_alarmtime DESC;

这个窗口函数写法逻辑更清晰，也更容易维护，推荐在支持的数据库中使用。

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