如何在单行中获得统一输出表

Question

我已经创建了示例数据库

create table items (Order_date date, frame char(2) , group_name varchar(50) , order_qty int(4) , receive_qty int(4));

insert into items(Order_date , frame , group_name , order_qty ,receive_qty) values 
("2021-3-10" , "am" , "books" , 4280 , 4000), 
("2021-3-12" , "pm" , "notebooks" , 3259 , 3100), 
("2021-3-14" , "pm" , "erasers" , 2828 , 2500), 
("2021-3-15" , "am" , "books" , 3147 , 3088), 
("2021-3-16" , "pm" , "notebooks" , 2897 , 2700), 
("2021-3-19" , "am" , "notebooks" , 4793 , 3030), 
("2021-3-21" , "pm" , "erasers" , 3317 , 3100);

#查询以获得填充率

select group_name , (sum(receive_qty)/sum(order_qty)*100) as Fill_Rate 
from items 
group by group_name;

#查询以获得帧为AM时的填充率

select group_name , (sum(receive_qty)/sum(order_qty)*100) as AM_Fill_Rate 
from items 
where frame = "am" 
group by group_name;

#查询以获得2021年3月16日的填充率

select group_name , (sum(receive_qty)/sum(order_qty)*100) as Fill_Rate_post_16Mar 
from items 
where Order_date >= "2021-03-16" 
group by group_name;

如何在单表中一次性运行Fill_Rate、AM_Fill_Rate和Fill_Rate_post_16Mar？？因为我可以通过单独运行以上的3个查询来获得所有的3个查询，所以3次

请给我建议！

​

​

Answer 1

select group_name , 
       (sum(receive_qty)/sum(order_qty)*100) as Fill_Rate ,
       (sum(case when frame = "am" then receive_qty else 0 end)/sum(case when frame = "am" then order_qty else 0 end)*100) as AM_Fill_Rate 
       (sum(case when Order_date >= "2021-03-16" then receive_qty else 0 end)/sum(case when Order_date >= "2021-03-16" then order_qty else 0 end)*100) as Fill_Rate_post_16Mar 
from items 
group by group_name;