mysql分组后再按照字段枚举值分别统计每种值出现的数量

原始sql

SELECT 
	u.user_id,
	u.user_name,
	u.dept_name,
	u.phone,
	u.job_num,
	u.duty_name,
	s.DAY AS DAY,
	s.type  as type,
	tt.time AS time,
	count(case when s.type = 0 then 0 else null end) as screenshotCount,
	count(case when s.type = 1 then 1 else null end) as screencapCount
	FROM user_screenshot_info s
	LEFT JOIN sys_user u
	ON u.user_id = s.user_id
	LEFT JOIN (SELECT user_id, max(time) AS time, DAY FROM user_screenshot_info GROUP BY user_id, DAY) tt
	ON tt.user_id = s.user_id
	AND s.DAY = tt.DAY
	GROUP BY
		s.user_id,
		s.DAY
	ORDER BY
		tt.time DESC

分析

• 先根据人和天维度求出用户每天最新产生的数据时间

LEFT JOIN (SELECT user_id, max(time) AS time, DAY FROM user_screenshot_info GROUP BY user_id, DAY) tt
	ON tt.user_id = s.user_id
	AND s.DAY = tt.DAY
	GROUP BY
		s.user_id,
		s.DAY

• 再在每天产生的数据中的根据type=0和type=1两种类型分别统计数量(count一定要在case when的前面，否则会出现数量合并的现象)

count(case when s.type = 0 then 0 else null end) as screenshotCount,
count(case when s.type = 1 then 1 else null end) as screencapCount

结果