SQL统计案例

SELECT

dr_id,
dr_name as '区域名称',

#早餐
SUM(IF(is_muslim=1 AND meal_type=1,order_number,0)) as '早餐清真餐总数',
SUM(IF(is_muslim=0 AND meal_type=1,order_number,0)) as '早餐非清真餐总数',
SUM(IF(is_muslim=1 AND order_status=3 AND meal_type=1,order_number, 0)) as '早餐清真餐已核销总数',
SUM(IF(is_muslim=1 AND order_status in (1,4) AND meal_type=1,order_number, 0)) as '早餐清真餐未核销总数',
SUM(IF(is_muslim=0 AND order_status=3 AND meal_type=1,order_number, 0)) as '早餐非清真餐已核销总数',
SUM(IF(is_muslim=0 AND order_status in (1,4) AND meal_type=1,order_number, 0)) as '早餐非清真餐未核销总数',

#午饭
SUM(IF(is_muslim=1 AND meal_type=2,order_number,0)) as '午餐清真餐总数',
SUM(IF(is_muslim=0 AND meal_type=2,order_number,0)) as '午餐非清真餐总数',
SUM(IF(is_muslim=1 AND order_status=3 AND meal_type=2,order_number, 0)) as '午餐清真餐已核销总数',
SUM(IF(is_muslim=1 AND order_status in (1,4) AND meal_type=2,order_number, 0)) as '午餐清真餐未核销总数',
SUM(IF(is_muslim=0 AND order_status=3 AND meal_type=2,order_number, 0)) as '午餐非清真餐已核销总数',
SUM(IF(is_muslim=0 AND order_status in (1,4) AND meal_type=2,order_number, 0)) as '午餐非清真餐未核销总数',

#晚餐
SUM(IF(is_muslim=1 AND meal_type=3,order_number,0)) as '晚餐清真餐总数',
SUM(IF(is_muslim=0 AND meal_type=3,order_number,0)) as '晚餐非清真餐总数',
SUM(IF(is_muslim=1 AND order_status=3 AND meal_type=3,order_number, 0)) as '晚餐清真餐已核销总数',
SUM(IF(is_muslim=1 AND order_status in (1,4) AND meal_type=3,order_number, 0)) as '晚餐清真餐未核销总数',
SUM(IF(is_muslim=0 AND order_status=3 AND meal_type=3,order_number, 0)) as '晚餐非清真餐已核销总数',
SUM(IF(is_muslim=0 AND order_status in (1,4) AND meal_type=3,order_number, 0)) as '晚餐非清真餐未核销总数'

FROM orders WHERE `status`=0 AND order_status IN (1,3,4) AND site_id=216 AND order_food_time>='2024-01-10 00:00:00' AND order_food_time<='2024-01-10 23:59:59' GROUP BY dr_id