员工考勤统计怎么写sql语句，查询出勤率，迟到次数，早退次数，旷工次数

SELECT 员工ID, COUNT(CASE WHEN 状态 = '出勤' THEN 1 END) AS 出勤次数, COUNT(CASE WHEN 状态 = '迟到' THEN 1 END) AS 迟到次数, COUNT(CASE WHEN 状态 = '早退' THEN 1 END) AS 早退次数, COUNT(CASE WHEN 状态 = '旷工' THEN 1 END) AS 旷工次数, (COUNT(CASE WHEN 状态 = '出勤' THEN 1 END) * 1.0 / COUNT(*)) * 100 AS 出勤率 FROM 考勤表 GROUP BY 员工ID;