最近老婆的公司天通，關(guān)閉了OA系統(tǒng)中莫瞬，各類打卡時(shí)間數(shù)據(jù)統(tǒng)計(jì)的功能雄卷，為了不麻煩老婆手算搓蚪，就做了一個(gè)簡單的打卡系統(tǒng)，方便自動(dòng)統(tǒng)計(jì)老婆想要知道的各類數(shù)據(jù)丁鹉。
做的過程中就遇到了幾個(gè)還挺有意思的SQL妒潭，這里寫成一篇博文，方便后期練習(xí)~
Tip：需要答案的盆友可以訪問 參考答案的鏈接揣钦，密碼是123456~
建表語句
drop table if exists fx67ll_punch_log;
create table fx67ll_punch_log (
punch_id bigint(20) not null auto_increment comment '打卡記錄主鍵',
punch_type char(1) comment '打卡類型（1代表上班 2代表下班）',
punch_remark varchar(1023) default '' comment '打卡記錄備注',
del_flag char(1) default '0' comment '刪除標(biāo)志（0代表存在 2代表刪除）',
user_id bigint(20) comment '用戶ID',
create_by varchar(64) default '' comment '記錄創(chuàng)建者',
create_time datetime comment '記錄創(chuàng)建時(shí)間',
update_by varchar(64) default '' comment '記錄更新者',
update_time datetime comment '記錄更新時(shí)間',
primary key (punch_id)
) engine=innodb auto_increment=1 comment = '打卡記錄表';
插入測試數(shù)據(jù)
INSERT INTO `ruoyi-mysql`.fx67ll_punch_log (punch_type,punch_remark,del_flag,user_id,create_by,create_time,update_by,update_time) VALUES
('1','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-06 00:00:00'),
('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-06 21:00:00'),
('1','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-08 15:00:00'),
('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-08 18:19:00'),
('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-08 21:00:00'),
('1','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-10 20:50:00'),
('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-10 21:44:00'),
('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-10 22:50:00'),
('1','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-11 10:00:00'),
('2','','0',1,'fx67ll','2023-12-12 19:49:41','fx67ll','2023-12-11 20:00:00'),
('1','','0',101,'user','2023-12-13 17:03:22','user','2023-12-13 00:06:00'),
('2','e?e?e','0',101,'user','2023-12-13 17:03:14','user','2023-12-11 01:01:00'),
('1','123','0',1,'fx67ll','2023-12-14 09:53:54','fx67ll','2023-12-14 09:53:50'),
('2','','0',101,'user','2024-03-13 17:49:16','user','2024-03-13 17:49:00'),
('1','324','0',101,'user','2024-03-21 11:22:16','user','2024-03-21 11:22:17'),
('2','','0',101,'user','2024-03-21 11:22:43','user','2024-03-21 22:22:39'),
('2','','0',1,'fx67ll','2024-03-30 20:01:10','fx67ll','2024-03-30 20:01:00'),
('1','','0',1,'fx67ll','2024-04-30 15:01:16','fx67ll','2024-04-30 00:01:06'),
('2','','0',1,'fx67ll','2024-04-30 15:01:25','fx67ll','2024-04-30 23:01:16'),
('1','','0',1,'fx67ll','2024-04-30 15:01:31','fx67ll','2024-04-24 15:01:25'),
('1','','0',101,'user','2024-05-03 02:39:33','user','2024-05-03 02:39:29'),
('1','','0',101,'user','2024-05-03 02:39:41','user','2024-05-04 00:39:33'),
('2','123','0',101,'user','2024-05-03 02:39:52','user','2024-05-04 23:39:41');
問題一：統(tǒng)計(jì)每個(gè)用戶每個(gè)月的工作總時(shí)長雳灾、總打卡天數(shù)、有效打卡天數(shù)以及日均工時(shí)
需要得到如下的統(tǒng)計(jì)數(shù)據(jù)冯凹，每個(gè)字段的含義分別是：
-
punch_user：打卡的用戶 -
punch_month：本條記錄統(tǒng)計(jì)的打卡月份 -
total_work_hours：當(dāng)月的工作總時(shí)長谎亩，小時(shí)為單位 -
total_work_minutes：當(dāng)月的工作總時(shí)長，分鐘為單位 -
total_work_seconds：當(dāng)月的工作總時(shí)長谈竿，秒為單位 -
total_punch_days：當(dāng)月的總打卡天數(shù)，只要有打卡記錄就算摸吠，可能只打了上班卡空凸，或者只打了下班卡，但是寸痢，沒有打卡記錄的天數(shù)則不算 -
total_work_days：當(dāng)月的有效打卡天數(shù)呀洲，必須滿足條件，既有上班打卡記錄啼止，又有下班打卡記錄道逗，才算一個(gè)有效打卡天數(shù) -
work_hours_per_day：當(dāng)月的日均工時(shí)，小時(shí)為單位
punch_user|punch_month|total_work_hours|total_work_minutes|total_work_seconds|total_punch_days|total_work_days|work_hours_per_day|
----------+-----------+----------------+------------------+------------------+----------------+---------------+------------------+
fx67ll |2023-12 | 39.0000| 2340| 140400| 5| 4| 9.75000000|
fx67ll |2024-03 | | | | 1| 0| |
fx67ll |2024-04 | 23.0028| 1380| 82810| 2| 1| 23.00277778|
user |2023-12 | | | | 2| 0| |
user |2024-03 | 11.0061| 660| 39622| 2| 1| 11.00611111|
user |2024-05 | 23.0022| 1380| 82808| 2| 1| 23.00222222|
問題二：統(tǒng)計(jì)每個(gè)用戶當(dāng)月的只打了一次卡的缺卡記錄
需要得到如下的統(tǒng)計(jì)數(shù)據(jù)献烦，每個(gè)字段的含義分別是：
-
punch_user：缺卡的用戶 -
punch_month：本條記錄的缺卡月份 -
punch_day：本條記錄的缺卡日期 -
lost_punch_type：本條記錄的缺卡類型滓窍，需要輸出是上班缺卡還是下班缺卡
punch_user|punch_month|punch_day |lost_punch_type|
----------+-----------+----------+---------------+
fx67ll |2023-12 |2023-12-14|下班缺卡 |
fx67ll |2024-03 |2024-03-30|上班缺卡 |
fx67ll |2024-04 |2024-04-24|下班缺卡 |
user |2023-12 |2023-12-11|上班缺卡 |
user |2023-12 |2023-12-13|下班缺卡 |
user |2024-03 |2024-03-13|上班缺卡 |
user |2024-05 |2024-05-03|下班缺卡 |
我是 fx67ll.com，如果您發(fā)現(xiàn)本文有什么錯(cuò)誤巩那，歡迎在評論區(qū)討論指正吏夯，感謝您的閱讀！
如果您喜歡這篇文章即横，歡迎訪問我的 本文github倉庫地址噪生，為我點(diǎn)一顆Star，Thanks~ :)
轉(zhuǎn)發(fā)請注明參考文章地址东囚，非常感謝６逅浴！！
