mysql - 查询我的 sql 数据库

Question

我有两个不同的查询:

SELECT Nazione,
       count(*) as Gold
  FROM `SummerTotalMedal` 
 where Medaglia = 'gold'
   and Sport = 'Athletics' 
 group by Nazione
 order by Gold DESC

和

SELECT Nazione, 
       count(*) as Silver
  FROM `SummerTotalMedal` 
 where Medaglia = 'silver'
   and Sport = 'Athletics' 
 group by Nazione
 order by Silver DESC

有没有办法加入这个查询，以便我在两个不同的列上得到两个不同的输出？

Answer 1

您可以使用相同的条件聚合

select 
Nazione,
sum(case when Medaglia ='gold' and Sport='Athletics' then 1 else 0 end) as Gold,
sum(case when Medaglia ='silver' and Sport='Athletics' then 1 else 0 end) as Silver
from SummerTotalMedal
group by Nazione