时间戳

首页 > 解决方案 > 具有正确时间的 MySQL 出勤 IN & OUT 列

mysql - 具有正确时间的 MySQL 出勤 IN & OUT 列

问题描述

我有一个考勤数据库，只要该人不工作超过 2 个日期，它就可以正常工作。我想使用 IN 和 OUT 系统进行记录，但我不知道如何做最后一步，我在论坛上看到的内容在 MySQL 上不起作用，或者我在那里做错了什么。这是我的数据库和查询。BTW 数据库是使用 PHPmyadmin 和 MySQL Workbench 构建的。

CREATE TABLE `entries` (
  `indexing` int(11) NOT NULL,
  `emp_id` int(5) NOT NULL,
  `Date` datetime DEFAULT current_timestamp()    ) ;



INSERT INTO `entries` (`indexing`, `emp_id`, `Date`) VALUES
(61, 1, '2020-07-07 05:41:36'),
(62, 1, '2020-07-07 05:44:21'),
(63, 2, '2020-07-07 05:44:36'),
(64, 3, '2020-07-07 05:49:23'),
(65, 2, '2020-07-07 05:49:39'),
(66, 3, '2020-07-07 05:50:00'),
(67, 4, '2020-07-07 09:56:51'),
(68, 5, '2020-07-07 09:57:13'),
(69, 3, '2020-07-07 09:57:18'),
(70, 2, '2020-07-07 09:57:28'),
(71, 1, '2020-07-07 09:57:42'),
(72, 4, '2020-07-07 09:57:49'),
(73, 5, '2020-07-07 09:59:38'),
(74, 1, '2020-07-08 05:59:42'),
(75, 2, '2020-07-08 06:00:05'),
(76, 3, '2020-07-08 06:38:20'),
(77, 1, '2020-07-08 09:47:43'),
(78, 4, '2020-07-08 09:56:14'),
(79, 5, '2020-07-08 09:56:47'),
(80, 1, '2020-07-08 09:56:59'),
(81, 3, '2020-07-08 09:57:34'),
(82, 2, '2020-07-08 09:58:07'),
(83, 4, '2020-07-08 09:58:11'),
(84, 5, '2020-07-08 09:59:20'),
(85, 5, '2020-07-08 09:59:50'),
(86, 4, '2020-07-08 11:08:36'),
(87, 3, '2020-07-08 11:09:30');

CREATE TABLE `user` (
  `emp_id` int(5) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `company` set('First','second') NOT NULL DEFAULT 'First',
  `department` set('Outbound','Inbound','UE','Returns','QC','Cleaner','Admin','IT 
   Technician','Supervisor','Manager') NOT NULL,
  `driver` set('PPT','VNA','HLOP','CB','PPT VNA HLOP','PPT HLOP','PPT CB') DEFAULT NULL
) ;


INSERT INTO `user` (`emp_id`, `Name`, `company`, `department`, `driver`) VALUES
(1, 'Micinka', 'second', 'IT Technician', ''),
(2, 'Dusbica', 'First', 'IT Technician', ''),
(3, 'Klaudocka', 'First', 'Returns', ''),
(4, 'Patrycginis', 'First', 'Cleaner', ''),
(5, 'Stuistow', 'First', 'Cleaner', '');


--
ALTER TABLE `entries`
  ADD PRIMARY KEY (`indexing`),
  ADD KEY `emp_id` (`emp_id`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
  ADD PRIMARY KEY (`emp_id`);


-- Constraints for table `entries`
--
ALTER TABLE `entries`
  ADD CONSTRAINT `entries_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `user` (`emp_id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

这是查询，最后一个是我希望表格的样子，但输入和输出时间正确，现在两者都相同。

select entries.emp_id, entries.Date, dense_rank() over (partition by entries.emp_id order by entries.indexing) % 2 AS 'IN and OUT' from entries;
  
    drop table report_inout;
        create view report_inout as select entries.emp_id, entries.Date,
        CASE WHEN DENSE_RANK() OVER (PARTITION BY entries.emp_id ORDER BY entries.Date) % 2 = 0
             THEN 'OUT' ELSE 'IN' END AS `IN and OUT`         
    FROM entries
    ORDER BY
        entries.indexing;
 
select date_format(report_inout.Date,'%d/%M/%Y') as `Date`,user.Name, time_format(report_inout.Date,'%H:%i:%s')  as `IN`, time_format(report_inout.Date,'%H:%i:%s')  as `OUT`,
    user.company as Company,user.department as Department from report_inout
    join user on user.emp_id = report_inout.emp_id
    group by user.Name, report_inout.`In and Out`;

这是我发布的查询的结果。

emp_id;"Date";"IN and OUT"
1;"2020-07-07 05:41:36";"IN"
1;"2020-07-07 05:44:21";"OUT"
2;"2020-07-07 05:44:36";"IN"
3;"2020-07-07 05:49:23";"IN"
2;"2020-07-07 05:49:39";"OUT"
3;"2020-07-07 05:50:00";"OUT"
4;"2020-07-07 09:56:51";"IN"
5;"2020-07-07 09:57:13";"IN"
3;"2020-07-07 09:57:18";"IN"
2;"2020-07-07 09:57:28";"IN"
1;"2020-07-07 09:57:42";"IN"
4;"2020-07-07 09:57:49";"OUT"
5;"2020-07-07 09:59:38";"OUT"
1;"2020-07-08 05:59:42";"OUT"
2;"2020-07-08 06:00:05";"OUT"
3;"2020-07-08 06:38:20";"OUT"
1;"2020-07-08 09:47:43";"IN"
4;"2020-07-08 09:56:14";"IN"
5;"2020-07-08 09:56:47";"IN"
1;"2020-07-08 09:56:59";"OUT"
3;"2020-07-08 09:57:34";"IN"
2;"2020-07-08 09:58:07";"IN"
4;"2020-07-08 09:58:11";"OUT"
5;"2020-07-08 09:59:20";"OUT"
5;"2020-07-08 09:59:50";"IN"

最后一个查询是这个，但在 IN 和 OUT 中总是相同的时间

Date;"Name";"IN";"OUT";"Company";"Department"
08/July/2020;"Dusbica";"09:58:07";"09:58:07";"First";"IT Technician"
08/July/2020;"Dusbica";"06:00:05";"06:00:05";"First";"IT Technician"
08/July/2020;"Klaudocka";"09:57:34";"09:57:34";"First";"Returns"
08/July/2020;"Klaudocka";"11:09:30";"11:09:30";"First";"Returns"
08/July/2020;"Micinka";"09:47:43";"09:47:43";"second";"IT Technician"
08/July/2020;"Micinka";"09:56:59";"09:56:59";"second";"IT Technician"
08/July/2020;"Patrycginis";"11:08:36";"11:08:36";"First";"Cleaner"
08/July/2020;"Patrycginis";"09:58:11";"09:58:11";"First";"Cleaner"
08/July/2020;"Stuistow";"09:59:50";"09:59:50";"First";"Cleaner"
08/July/2020;"Stuistow";"09:59:20";"09:59:20";"First";"Cleaner"

标签： mysqlsql

解决方案

假如说：

  1. 每个单独的第一条记录emp_id是 IN 事件
  2. 没有丢失的事件
WITH cte AS ( SELECT emp_id, `Date`, 
                     ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY `Date`) - 1 rn
              FROM entries )
SELECT t1.emp_id, user.name, t1.`Date` in_date, t2.`Date` out_date
FROM user
JOIN cte t1 ON user.emp_id = t1.emp_id
LEFT JOIN cte t2 ON t1.emp_id = t2.emp_id
                AND t1.rn DIV 2 = t2.rn DIV 2
                AND t2.rn MOD 2
WHERE NOT t1.rn MOD 2
ORDER BY emp_id, in_date;

小提琴

主意。

我们从零开始分别枚举每个员工的所有行。所以第一个 IN 是 0，第一个 OUT 是 1，第二个 IN 是 2，依此类推。

您可以看到匹配的 IN 和 OUT 事件在整数除以 2 后将给出相同的结果。IN 的提醒将为 0，而 OUT 的提醒将为 1。

这足以正确加入。

CTE 表的第二个副本正在使用 LEFT 连接进行连接，因为最后一个 IN 行可能没有相应的 OUT 行 - 这意味着该员工现在存在于对象中。out_date在这种情况下，最后一行将在列中包含 NULL 。

推荐阅读