今天遇到一个功能,需要在两个结构一样的表中统计预约方式的个数,关键点在于union all 和sum的使用
1 select sum(y1) as y,a.name 2 from(SELECT 3 count(1) as y1, 4 sdd.dict_label as name 5 FROM 6 ars_appointment_info aai 7 JOIN sys_dict_data sdd 8 on sdd.dict_value=aai.AppointmentChannel AND sdd.dict_type = ‘ars_appointment_info_AppointmentChannel‘ 9 where AppointmentDate >=‘2020-6-2‘ 10 and AppointmentDate <= ‘2020-6-8‘ 11 GROUP BY 12 AppointmentChannel union all 13 SELECT 14 count(1) as y2, 15 sdd.dict_label as name 16 FROM 17 ars_appointment_info_non aai 18 JOIN sys_dict_data sdd 19 on sdd.dict_value=aai.AppointmentChannel AND sdd.dict_type = ‘ars_appointment_info_AppointmentChannel‘ 20 where AppointmentDate >=‘2020-6-2‘ 21 and AppointmentDate <= ‘2020-6-8‘ 22 GROUP BY 23 AppointmentChannel) as a 24 GROUP BY a.name