SQL使用基本函数实现不同类型的计数统计

 人员    、    午餐次数/月    、    晚餐次数/月    、    次日早餐次数/月    、    总次数/月
 张三    、    12                   、      65                 、    25                      、     102 

 

SELECT user_name,
COUNT(`reserve_type`) AS totalNum,
SUM(CASE WHEN `reserve_type`= 1 THEN 1 ELSE 0 END)AS lunchNum,
SUM(CASE WHEN `reserve_type`= 2 THEN 1 ELSE 0 END)AS dinnerNum,
SUM(CASE WHEN `reserve_type`= 3 THEN 1 ELSE 0 END)AS breakNum
FROM `jg_meal_code` AS meal
LEFT JOIN jg_oa_user AS oa
ON meal.apply_person_id=oa.id
WHERE 1 = 1 AND meal.organ_id='341d5586978649c0bd8296642cf0da7f'
GROUP BY user_name

参考链接http://www.cnblogs.com/xiao-momo/p/5810335.html