如何防止在具有多个选择子句的内部联接中出现重复列

Question

select * from
(select date, gen_city_id, min(temp) as min_temp, max(temp) as max_temp from current_weather group by date, gen_city_id order by date) cw
inner join
(select gen_city_id, forecast_date, array_agg(temp) from forecast where forecast_date < current_date group by gen_city_id, forecast_date) f
on cw.gen_city_id = f.gen_city_id and cw.date = f.forecast_date;

上述查询有效，但gen_city_id列和date/forecast_date列是从两个表中选择的。在我的结果集中，如何防止两个表中的重复列？

如果我尝试从任何一个表的选择原因中删除列，则查询错误。

Answer 1

以这种方式更改查询。您可以指定要在结果集中获取的字段：

select cw.*,f.temp from
(select date, gen_city_id, min(temp) as min_temp, max(temp) as max_temp from current_weather group by date, gen_city_id order by date) cw
inner join
(select gen_city_id, forecast_date, array_agg(temp) temp from forecast where forecast_date < current_date group by gen_city_id, forecast_date) f
on cw.gen_city_id = f.gen_city_id and cw.date = f.forecast_date;

Answer 2

您可以使用using子句：

select *
from (select date, gen_city_id, min(temp) as min_temp, max(temp) as max_temp
from current_weather
group by date, gen_city_id order by date
) cw join
(select gen_city_id, forecast_date as date, array_agg(temp)
from forecast
where forecast_date < current_date
group by gen_city_id, forecast_date
) f
using (gen_city_id, date) ;

这将删除using子句中包含的重复列。

不过，一般来说，我建议单独列出所有列。