mysql group by 分组查询后 按某列再分组统计

表1:sys_user

表2:mpkpi_attendance_detail

需求统计用户每种考勤状态的数量

• 先按用户id和考勤状态分组查询

SELECT
	u.user_id,
	u.user_name,
	u.nick_name,
	signIn_state,
	count(signIn_state) as counts
FROM
	mpkpi_attendance_detail a
	LEFT JOIN sys_user u ON a.user_id = u.user_id
GROUP BY
	signIn_state,
	user_id

• 最终结果 对查询结果再分组统计

SELECT
	t.nick_name,
	SUM( CASE WHEN t.`signIn_state` = 1 THEN t.counts ELSE 0 END ) AS 'attendance_number',
	SUM( CASE WHEN t.`signIn_state` = 2 THEN t.counts ELSE 0 END ) AS 'generalHoliday_number',
	SUM( CASE WHEN t.`signIn_state` = 2 THEN t.counts ELSE 0 END ) AS 'overtime_number',
	SUM( CASE WHEN t.`signIn_state` = 4 THEN t.counts ELSE 0 END ) AS 'evection_number',
	SUM( CASE WHEN t.`signIn_state` = 5 THEN t.counts ELSE 0 END ) AS 'takeWorkingDay_number',
	SUM( CASE WHEN t.`signIn_state` = 6 THEN t.counts ELSE 0 END ) AS 'absenteeism_number',
	SUM( CASE WHEN t.`signIn_state` = 7 THEN t.counts ELSE 0 END ) AS 'annualLeave_number',
	SUM( CASE WHEN t.`signIn_state` = 8 THEN t.counts ELSE 0 END ) AS 'leave_number'
FROM
	(
	SELECT
		u.user_id,
		u.user_name,
		u.nick_name,
		signIn_state,
		count( signIn_state ) AS counts 
	FROM
		mpkpi_attendance_detail a
		LEFT JOIN sys_user u ON a.user_id = u.user_id 
	GROUP BY
		signIn_state,
		user_id 
	) t 
GROUP BY
	t.nick_name