需求描述

表名:employeesign

列名:emp_date和emp_status

求一月份有多少人請假，多少人簽到等

最終得到如下結果：

解決方法

?通過SQL里的聚合函數結合CASE WHEN來完成統計。

SQL代碼

-- 1 創建表(以mysql版為例) CREATE TABLE emp_temp ( id varchar(30), create_by varchar(10), create_time datetime, update_by varchar(10), update_time datetime, sys_org_code varchar(10), emp_name varchar(20), emp_date datetime, emp_status SMALLINT, emp_pic varchar(30), remark varchar(20) )-- 2 按照年月份、考勤狀態統計 SELECT DATE_FORMAT(emp_date,'%Y-%m') '年月份', COUNT(CASE WHEN emp_status = 0 THEN 1 ELSE NULL END) '正常簽到', COUNT(CASE WHEN emp_status = 1 THEN 1 ELSE NULL END) '未簽到', COUNT(CASE WHEN emp_status = 2 THEN 1 ELSE NULL END) '缺勤', COUNT(CASE WHEN emp_status = 3 THEN 1 ELSE NULL END) '請假' FROM ( SELECT max(emp_date) emp_date,id,emp_status FROM emp_temp GROUP BY id )A GROUP BY DATE_FORMAT(emp_date,'%Y-%m')-- 3 結果 /* 年月份 正常簽到 未簽到 缺勤 請假 2020-09 1 0 0 0 2020-12 0 1 1 2 2021-01 6 3 12 10 */-- 4 附錄初始化數據腳本 INSERT INTO `emp_temp` VALUES ('1442771447134928906', 'admin', '2021-09-28 16:41:33', NULL, NULL, 'A01', '1442503674139873284', '2020-09-23 16:34:50', 0, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1442771447164289029', 'admin', '2021-09-28 16:41:33', NULL, NULL, 'A01', '1442503674139873286', '2020-12-23 10:34:23', 1, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1442771447164289030', 'admin', '2021-09-28 16:41:33', NULL, NULL, 'A01', '1442503674148261889', '2020-12-23 10:34:24', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1442771447164289031', 'admin', '2021-09-28 16:41:33', NULL, NULL, 'A01', '1442503674148261890', '2020-12-23 10:34:25', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1442771447164289032', 'admin', '2021-09-28 16:41:33', NULL, NULL, 'A01', '1442503674148261891', '2020-12-23 10:34:26', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982938997579778', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674123096069', '2021-01-01 12:12:01', 1, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939018551298', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484674', '2021-01-01 12:12:02', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939022745601', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484675', '2021-01-01 12:12:03', 1, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939022745602', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484676', '2021-01-01 12:12:04', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939022745603', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484677', '2021-01-01 12:12:05', 0, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939022745604', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484678', '2021-01-01 12:12:06', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939031134210', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873282', '2021-01-01 12:12:07', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939031134211', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873283', '2021-01-01 12:12:08', 0, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939035328513', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873284', '2021-01-01 12:12:09', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939035328514', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873285', '2021-01-01 12:12:10', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939035328515', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873286', '2021-01-01 12:12:11', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939039522817', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674148261889', '2021-01-01 12:12:12', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939039522818', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674148261890', '2021-01-01 12:12:13', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939039522819', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674148261891', '2021-01-01 12:12:14', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939039522820', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674123096069', '2021-01-01 12:12:15', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939047911426', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484674', '2021-01-01 12:12:16', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939052105729', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484675', '2021-01-01 12:12:17', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939052105730', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484676', '2021-01-01 12:12:18', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939052105731', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484677', '2021-01-01 12:12:19', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939052105732', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484678', '2021-01-01 12:12:20', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939060494338', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873282', '2021-01-01 12:12:21', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939060494339', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873283', '2021-01-01 12:12:22', 0, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939060494340', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873284', '2021-01-01 12:12:23', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939060494341', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873285', '2021-01-01 12:12:24', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939060494342', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674139873286', '2021-01-01 12:12:25', 0, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939068882946', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674148261889', '2021-01-01 12:12:26', 2, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939068882947', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674148261890', '2021-01-01 12:12:27', 3, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939068882948', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674148261891', '2021-01-01 12:12:28', 0, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939068882949', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674123096069', '2021-01-01 12:12:29', 1, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939077271553', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484674', '2021-01-01 12:12:30', 0, NULL, '無'); INSERT INTO `emp_temp` VALUES ('1448982939077271554', 'admin', '2021-10-15 20:03:48', NULL, NULL, 'A01', '1442503674131484675', '2021-01-01 12:12:31', 3, NULL, '無');

總結

以上是生活随笔為你收集整理的SQL按照年月员工状态统计出勤情况的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。