行程温度获取需求：优先实时数据，无则取历史平均值

我来帮你搞定这个需求！咱们的目标是：为每个行程获取对应时段的实时温度，如果实时数据不可用，就计算该时段内的历史温度平均值。先明确一下用到的数据表结构：

数据表结构 #

Trip 表（行程信息） #

Location 表（地点信息） #

Weather 表（温度数据，按天存储） #

实现思路 & SQL 代码 #

核心思路是优先获取实时数据，实时数据缺失时 fallback 到时段平均值。这里的"实时数据"我默认定义为「行程时段内的最新温度记录」，如果你的业务里对"实时"有更严格的定义（比如当前时间正处于行程中，且取当天的温度），可以直接调整代码里的条件。

方案一：用 COALESCE 实现优先逻辑 #

这是最简洁的写法，利用COALESCE函数优先返回第一个非空结果：

SELECT
    t.id AS trip_id,
    l.name AS location_name,
    -- 先尝试拿实时温度，拿不到就取时段平均
    COALESCE(
        -- 子查询1：获取行程时段内的最新温度（模拟实时数据）
        (SELECT w.temperature
         FROM Weather w
         WHERE w.location_id = t.location_id
           AND w.date BETWEEN DATE(t.departure_time) AND DATE(t.arrival_time)
         -- 如果需要严格匹配「当前处于行程中」的实时，加上下面两行注释的条件
         -- AND CURDATE() = w.date 
         -- AND NOW() BETWEEN t.departure_time AND t.arrival_time
         ORDER BY w.date DESC
         LIMIT 1),
        -- 子查询2：实时数据缺失时，计算行程时段内的温度平均值
        (SELECT AVG(w.temperature)
         FROM Weather w
         WHERE w.location_id = t.location_id
           AND w.date BETWEEN DATE(t.departure_time) AND DATE(t.arrival_time))
    ) AS trip_temperature
FROM Trip t
JOIN Location l ON t.location_id = l.id;

方案二：用 CASE 做更灵活的判断 #

如果需要更明确的逻辑分支（比如判断整个时段是否有温度记录，再决定取实时还是全局平均），可以用CASE语句：

SELECT
    t.id AS trip_id,
    l.name AS location_name,
    CASE
        -- 先判断行程时段内是否有温度记录
        WHEN EXISTS (
            SELECT 1
            FROM Weather w
            WHERE w.location_id = t.location_id
              AND w.date BETWEEN DATE(t.departure_time) AND DATE(t.arrival_time)
        ) THEN
            -- 有记录时优先取最新的（实时）温度
            (SELECT w.temperature
             FROM Weather w
             WHERE w.location_id = t.location_id
               AND w.date BETWEEN DATE(t.departure_time) AND DATE(t.arrival_time)
             ORDER BY w.date DESC
             LIMIT 1)
        ELSE
            -- 完全没有时段记录时，取该地点所有历史温度的平均值（也可以返回NULL）
            (SELECT AVG(w.temperature) FROM Weather w WHERE w.location_id = t.location_id)
    END AS trip_temperature
FROM Trip t
JOIN Location l ON t.location_id = l.id;

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