postgresql SQL使用列的SUM INNER JOIN另一个表中的另一个列

z18hc3ub 于 2023-04-11 发布在 PostgreSQL

关注(0)|答案(1)|浏览(96)

当我尝试执行如下所示的查询时，我得到了这个错误
错误：列临时accdegreedays不存在
第7行：INNER JOIN ricepest.add ON ricepest.temp.accdegreedays = ric...
这是我的查询：

SELECT 
    ricepest.temp.state_id AS stateId, 
    ricepest.temp.district_id AS districtId, 
    ricepest.temp.date_id AS dateId, 
    ricepest.temp.tm_max AS maxTemp,
    ricepest.temp.tm_min AS minTemp, 
    ricepest.temp.mean AS Mean, 
    ricepest.states.state AS state, 
    ricepest.states.district AS district, 
    (ricepest.temp.mean - 9) AS DegreeDays,
    SUM(ricepest.temp.mean - 9) OVER (ORDER BY ricepest.temp.state_id, ricepest.temp.district_id, ricepest.temp.date_id, ricepest.temp.tm_max, ricepest.temp.tm_min, ricepest.temp.mean, ricepest.states.state, ricepest.states.district) AS accdegreedays
FROM 
    ricepest.temp
INNER JOIN 
    ricepest.states ON ricepest.temp.state_id = ricepest.states.state_id 
                    AND ricepest.temp.district_id = ricepest.states.district_id
INNER JOIN 
    ricepest.add ON ricepest.temp.accdegreedays = ricepest.add.accdegreedays
WHERE 
    ricepest.temp.state_id = 'AP' 
    AND ricepest.temp.district_id = 1 
    AND date_id >= '2000-01-01' AND date_id <= '2000-01-31'
GROUP BY 
    ricepest.temp.state_id, ricepest.temp.district_id,  
    ricepest.temp.date_id, ricepest.temp.tm_max, 
    ricepest.temp.tm_min, ricepest.temp.mean, 
    ricepest.states.state, ricepest.states.district;

postgresql

来源：https://stackoverflow.com/questions/75963452/sql-using-sum-of-the-column-inner-join-another-column-in-another-table

1条答案

按热度按时间

pbpqsu0x1#

按照你的说法（根据你发表的评论），一个选择可能是从该查询中删除与ADD表的联接，并使用剩下的作为CTE。然后在sum窗口函数结果中使用add.accdegreedays将其与ADD联接。
因为你没有聚合任何东西，你真的需要GROUP BY子句吗？窗口函数不需要它。至于sum本身，看起来你是按你能做的一切来排序的。你真的想这样做吗？
总之：我是这么说的：

with t_query as
(
SELECT 
    ricepest.temp.state_id AS stateId, 
    ricepest.temp.district_id AS districtId, 
    ricepest.temp.date_id AS dateId, 
    ricepest.temp.tm_max AS maxTemp,
    ricepest.temp.tm_min AS minTemp, 
    ricepest.temp.mean AS Mean, 
    ricepest.states.state AS state, 
    ricepest.states.district AS district, 
    (ricepest.temp.mean - 9) AS DegreeDays,
    SUM(ricepest.temp.mean - 9) OVER 
      (ORDER BY ricepest.temp.state_id, ricepest.temp.district_id, ricepest.temp.date_id, 
                ricepest.temp.tm_max, ricepest.temp.tm_min, ricepest.temp.mean, 
                ricepest.states.state, ricepest.states.district
      ) AS accdegreedays
FROM 
    ricepest.temp
INNER JOIN 
    ricepest.states ON ricepest.temp.state_id = ricepest.states.state_id 
                   AND ricepest.temp.district_id = ricepest.states.district_id
WHERE 
    ricepest.temp.state_id = 'AP' 
    AND ricepest.temp.district_id = 1 
    AND date_id >= '2000-01-01' AND date_id <= '2000-01-31'
)
select t_query.*
from t_query 
INNER JOIN 
    ricepest.add ON t_query.accdegreedays = ricepest.add.accdegreedays;

赞(0）回复(0）举报 2023-04-11

我来回答

postgresql SQL使用列的SUM INNER JOIN另一个表中的另一个列

1条答案

相关问题

热门标签

最新问答