我有以下疑问:
select year, avg(value) as winter_avg_Miami from AP_DATA where substring(series_id, 8) ='killowatts' and substring(series_id, 3,5) = 'Miami' and period IN ('M10','M11','M12') group by year;
select year, avg(value) as winter_avg_notMiami from AP_DATA where substring(series_id, 8) = 'killowatts' and substring(series_id, 3,5) != 'Miami' and period IN ('M10','M11','M12') group by year;
我想做的是不要买两张不同的桌子,一张像:
year winter_avg_Miami
2000 28.1
2001 30.2
另一张桌子像:
year winter_avg_notMiami
2000 40.1
2001 50.2
我只想把所有的信息放在一个表中,比如:
year winter_avg_Miami winter_avg_notMiami
2000 28.1 40.1
2001 30.2 50.2
如何编写查询才能完成此任务?
最佳答案
您可以在case
聚合中使用avg
语句:
select year,
avg(case when substring(series_id, 3,5) = 'Miami' then value end) as winter_avg_Miami,
avg(case when substring(series_id, 3,5) != 'Miami' then value end) as winter_avg_notMiami
from AP_DATA
where substring(series_id, 8) ='killowatts'
and period IN ('M10','M11','M12')
group by year
Condensed SQL Fiddle Demo
关于mysql - 如何在同一结果表中包含来自两个查询的信息?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/27236586/