mysql - 从 2 个 MySQL 表中检索 Rain Current Rate

Question

我有两个 SQL 表:

• 雨量计(id、lat、lng、desc)
• 下雨(id、pluviometer_id、日期、值)

表字段和结构是不言自明的。

唯一值得注意的一点是“rains”是一个cumulative表，即value字段是累积的降雨量，以及各自的日期 只是值(value) 增加的时刻。

考虑到所有这些，我想执行一个 SQL 查询，该查询获取所有雨量计及其属性加上一个字段的列表，其中雨量从给定日期到查询执行时间.

到目前为止我最好的尝试:

    SELECT 
    pluviometers.*, 
    lastDate, 
    lastValue,
    firstDate,
    firstValue,
    rain = 
    CASE firstDate
        WHEN NULL THEN 0
        ELSE (lastValue - firstValue) / (lastDate - firstDate)
    END

FROM pluviometers

LEFT JOIN (

    SELECT 
        h.pluviometer_id AS pid,
        MAX(h.date) AS lastDate, 
        h.value AS lastValue

    FROM rains h

    LEFT JOIN (

        SELECT 
            h2.pluviometer_id AS pid2,
            MIN(h2.date) AS firstDate, 
            h2.value AS firstValue 

            FROM rains h2
            WHERE h2.date > ###### GIVEN DATE ######

            GROUP BY pid2
            ORDER BY pid2 ASC

        ) AS p2 ON pid2 = h.pluviometer_id

    GROUP BY pid
    ORDER BY pid ASC

) AS p ON pid = pluviometers.id

GROUP BY pluviometers.id
ORDER BY pluviometers.id ASC

到目前为止，我从服务器收到 #1054 - Unknown column 'firstDate' in 'field list' 错误。

非常感谢任何帮助。提前致谢。

Answer 1

firstDate 和 firstValue 是从 h2 中选择的，以创建结果集 p2，但您不t 从组合结果集中选择 p2.firstDate 或 p2.firstValue rains h LEFT JOIN p2。

所以将它们添加到您的第一个子选择子句中:

... SELECT 
    h.pluviometer_id AS pid,
    MAX(h.date) AS lastDate, 
    h.value AS lastValue,
    p2.firstDate,
    p2.firstValue

FROM rains h ...

这将使它们对顶部的外部选择子句可见。

此外，更改用于选择 rain 字段的语法:

不是

rain = 
    CASE firstDate
        WHEN NULL THEN 0
        ELSE (lastValue - firstValue) / (lastDate - firstDate)

而是

CASE firstDate
    WHEN NULL THEN 0
    ELSE (lastValue - firstValue) / (lastDate - firstDate)
END AS rain