从2个MySQL表中获取当前的雨量。

I have two SQL tables:

我有两个SQL表:

pluviometers (id, lat, lng, desc)
雨量计(id, lat, lng, desc)
rains (id, pluviometer_id, date, value)
雨季(id, pluviometer_id, date, value)

Table fields and structure are self-explanatory.

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

The only remarkable point is that 'rains' is a cumulative table, i.e., value field is the cumulative amount of rain, and respective dates are just the moments when value increases.

唯一值得注意的一点是，“雨”是一个累积的表，即。，值场为累计降雨量，各日期为值增加的时刻。

With all of that in mind, I want to execute a SQL query which obtains the list of all pluviometers and their attributes plus one field, with the rain amount from a given date to the query execution time.

考虑到所有这些，我希望执行一个SQL查询，该查询获取所有pluviometer及其属性的列表以及一个字段，其中包含从给定日期到查询执行时间的雨量。

My best try, so far:

到目前为止，我尽了最大的努力:

    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

So far, I get a #1054 - Unknown column 'firstDate' in 'field list' error from the server.

到目前为止，我得到了一个#1054 -在“字段列表”错误中未知的列“firstDate”。

Any help is much appreciated. Thanks in advance.

非常感谢您的帮助。提前谢谢。

1 个解决方案

#1

firstDate and also firstValue are selected from h2 to create the result set p2, but you don't select p2.firstDate or p2.firstValue from the combined result set rains h LEFT JOIN p2.

从h2中选择firstDate和firstValue来创建结果集p2，但是您不选择p2。firstDate或p2。第一个值来自组合结果集雨h左连接p2。

So add them to your first sub-select clause here:

把它们添加到这里的第一个子选择子句中:

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

FROM rains h ...

This will make them visible to the outer select clause at the top.

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

Also, change the syntax for selecting the rain field:

此外，更改选择雨场的语法:

Not

不

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

but instead

而是

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

#1