我有以下疑问:

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/

10-10 23:36
查看更多