Mysql选择当天或第二天之间的最新记录-6ren

Mysql选择当天或第二天之间的最新记录

转载作者：行者123 更新时间：2023-11-29 15:42:29

我有一个员工每天进出时间的数据集，如下:

uuid    datetime            Status  
7   2019-08-07 07:15:56     Checkout
7   2019-08-06 19:06:00     Checkin
7   2019-08-06 19:05:49     Checkout
7   2019-08-06 08:49:57     Checkin
7   2019-08-06 08:49:46     Checkout
7   2019-08-05 19:00:52     Checkin
7   2019-08-04 17:10:13     Checkout
7   2019-08-04 08:50:04     Checkin
7   2019-08-04 08:49:47     Checkout
7   2019-08-03 18:10:23     Checkin
7   2019-08-03 18:10:13     Checkout
7   2019-08-03 08:41:44     Checkin
7   2019-08-03 08:41:31     Checkout
7   2019-08-02 19:04:55     Checkin
7   2019-08-02 19:04:43     Checkout
7   2019-08-02 08:54:06     Checkin
7   2019-08-02 08:53:49     Checkout
7   2019-08-01 18:55:05     Checkin
7   2019-08-01 07:16:47     Checkout
7   2019-07-31 18:28:11     Checkin
7   2019-07-31 07:15:24     Checkout
7   2019-07-30 19:05:45     Checkin
7   2019-07-30 19:05:33     Checkout
7   2019-07-30 08:56:11     Checkin
7   2019-07-30 08:56:00     Checkout

使用这些数据，我想计算一天的总工作小时数。默认时间为 07:00 至 16:00 以及 16:00 至次日 01:00。加时时间 01:00 至 06:00。

我的挑战是，当一名员工在 07:00 签到并工作到第二天早上 03:00(轮类+几个加类时间)时，我使用以下 SQL 来检索结账时间，但它仅在检查时返回结果发生在同一天。如果结帐时间为 03:00、3:30、4:00 等，我无法检索结果...

此外，可以进行不止一次的 checkin 和结账，但必须考虑最早的 checkin 和最晚的结账

在下面的查询中，我尝试检索结帐时间，它仅在当天存在结帐时间的情况下返回结果，而不是在午夜之后返回结果。

SELECT uuid, DATE_FORMAT(datetime, "%M %d %Y") AS outdate, DATE_FORMAT(datetime, "%H:%i") AS outtimes FROM attendance_logs WHERE status = 'Checkout' AND (DATE_FORMAT(datetime, "%Y-%m-%d %H:%i:%s") BETWEEN DATE_FORMAT(datetime, "%Y-%m-%d 13:00:00") AND DATE_FORMAT(DATE_ADD(datetime, INTERVAL 1 DAY), "%Y-%m-%d 09:30:00")) AND DATE_FORMAT(datetime, "%Y-%m-%d") < "2019-08-08" ORDER BY datetime DESC

预期输出(已于 2019 年 8 月 5 日编辑修复结果)

<小时/>

uuid    date      Checkin   Checkout
7         2019-08-06  08:49:57   07:15:56
7         2019-08-05  19:00:52   08:49:46
7         2019-08-04  08:50:04    17:10:13
7         2019-08-03  08:41:44    08:49:47
7         2019-08-02  08:54:06    08:41:31
7         2019-08-01  18:55:05    08:53:49
7         2019-07-31  07:16:47    18:28:11
7         2019-07-30  08:56:11    07:15:24

最终的SQL查询

 select attendance_logs.uuid
      , emp.name
      , dept.name AS department_name
      , DATE_FORMAT(attendance_logs.datetime
      , "%M %d %Y") AS day
      , cintimes.intimes AS cin 
      , couttimes.outtimes AS cout  
   from attendance_logs 
   left 
   join employees AS emp 
     on emp.uuid = attendance_logs.uuid 
    and emp.is_disabled = 0 
    and emp.is_valid = 1 
   left 
   join departments AS dept 
     on dept.id = attendance_logs.department_id 
    and dept.is_disabled = 0 
    and dept.is_valid = 1 
   left 
   join 
      ( SELECT uuid
             , DATE_FORMAT(datetime, "%M %d %Y") AS indate
             , DATE_FORMAT(datetime, "%H:%i") AS intimes 
          FROM  attendance_logs 
         WHERE status = 'Checkin' 
           AND DATE_FORMAT(datetime, "%Y-%m-%d") <= "2019-08-07" 
         GROUP 
            BY DATE_FORMAT(datetime, "%M %d %Y")
             , uuid 
         ORDER 
            BY datetime DESC ) AS cintimes 
     on cintimes.uuid = attendance_logs.uuid 
    and cintimes.indate = DATE_FORMAT(attendance_logs.datetime, "%M %d %Y") 
   left 
   join 
      ( SELECT uuid
             , DATE_FORMAT(datetime, "%M %d %Y") AS outdate
             , DATE_FORMAT(datetime, "%H:%i") AS outtimes 
          FROM attendance_logs 
         WHERE status = 'Checkout' 
           AND (DATE_FORMAT(datetime, "%Y-%m-%d %H:%i:%s") BETWEEN  DATE_FORMAT(datetime, "%Y-%m-%d 13:00:00") AND  DATE_FORMAT(DATE_ADD(datetime, INTERVAL 1 DAY), "%Y-%m-%d 09:30:00")) 
           AND DATE_FORMAT(datetime, "%Y-%m-%d") < "2019-08-07" 
         ORDER 
            BY datetime DESC ) AS couttimes 
     on couttimes.uuid = attendance_logs.uuid 
    and couttimes.outdate = DATE_FORMAT(attendance_logs.datetime, "%M %d %Y") 
  where attendance_logs.uuid = ? 
  group 
     by DATE_FORMAT(attendance_logs.datetime, "%M %d %Y")
      , attendance_logs.uuid 
  order 
     by attendance_logs.datetime desc

最佳答案

数据集:

SELECT * FROM my_table;
+------+---------------------+----------+
| uuid | datetime            | Status   |
+------+---------------------+----------+
|    7 | 2019-07-30 08:56:00 | Checkout |
|    7 | 2019-07-30 08:56:11 | Checkin  |
|    7 | 2019-07-30 19:05:33 | Checkout |
|    7 | 2019-07-30 19:05:45 | Checkin  |
|    7 | 2019-07-31 07:15:24 | Checkout |
|    7 | 2019-07-31 18:28:11 | Checkin  |
|    7 | 2019-08-01 07:16:47 | Checkout |
|    7 | 2019-08-01 18:55:05 | Checkin  |
|    7 | 2019-08-02 08:53:49 | Checkout |
|    7 | 2019-08-02 08:54:06 | Checkin  |
|    7 | 2019-08-02 19:04:43 | Checkout |
|    7 | 2019-08-02 19:04:55 | Checkin  |
|    7 | 2019-08-03 08:41:31 | Checkout |
|    7 | 2019-08-03 08:41:44 | Checkin  |
|    7 | 2019-08-03 18:10:13 | Checkout |
|    7 | 2019-08-03 18:10:23 | Checkin  |
|    7 | 2019-08-04 08:49:47 | Checkout |
|    7 | 2019-08-04 08:50:04 | Checkin  |
|    7 | 2019-08-04 17:10:13 | Checkout |
|    7 | 2019-08-05 19:00:52 | Checkin  |
|    7 | 2019-08-06 08:49:46 | Checkout |
|    7 | 2019-08-06 08:49:57 | Checkin  |
|    7 | 2019-08-06 19:05:49 | Checkout |
|    7 | 2019-08-06 19:06:00 | Checkin  |
|    7 | 2019-08-07 07:15:56 | Checkout |
+------+---------------------+----------+

这是部分结果...

 SELECT x.uuid
      , x.datetime checkin
      , MIN(y.datetime) checkout
   FROM my_table x 
   LEFT 
   JOIN my_table y 
     ON y.datetime > x.datetime 
    AND y.status = 'checkout' 
  WHERE x.status = 'checkin' 
  GROUP 
     BY x.datetime;

+------+---------------------+---------------------+
| uuid | checkin             | checkout            |
+------+---------------------+---------------------+
|    7 | 2019-07-30 08:56:11 | 2019-07-30 19:05:33 |
|    7 | 2019-07-30 19:05:45 | 2019-07-31 07:15:24 |

|    7 | 2019-07-31 18:28:11 | 2019-08-01 07:16:47 |

|    7 | 2019-08-01 18:55:05 | 2019-08-02 08:53:49 |

|    7 | 2019-08-02 08:54:06 | 2019-08-02 19:04:43 |
|    7 | 2019-08-02 19:04:55 | 2019-08-03 08:41:31 |

|    7 | 2019-08-03 08:41:44 | 2019-08-03 18:10:13 |
|    7 | 2019-08-03 18:10:23 | 2019-08-04 08:49:47 |

|    7 | 2019-08-04 08:50:04 | 2019-08-04 17:10:13 |

|    7 | 2019-08-05 19:00:52 | 2019-08-06 08:49:46 |

|    7 | 2019-08-06 08:49:57 | 2019-08-06 19:05:49 |
|    7 | 2019-08-06 19:06:00 | 2019-08-07 07:15:56 |
+------+---------------------+---------------------+

我仍然无法遵循让我们从这里达到您想要的结果的逻辑。

编辑:

这看起来非常像您想要的结果，但我不知道这是否真的是您想要的......

SELECT uuid
     , MIN(checkin) checkin
     , MAX(checkout) checkout
  FROM
     (  SELECT x.uuid
             , x.datetime checkin
             , MIN(y.datetime) checkout
             , DATE(x.datetime-INTERVAL 8 HOUR) dt
          FROM my_table x
          LEFT
          JOIN my_table y
            ON y.uuid = x.uuid
           AND y.datetime > x.datetime
           AND y.status = 'checkout'
         WHERE x.status = 'checkin'
         GROUP
            BY x.datetime
     ) a
 GROUP
    BY uuid
     , dt;
+------+---------------------+---------------------+
| uuid | checkin             | checkout            |
+------+---------------------+---------------------+
|    7 | 2019-07-30 08:56:11 | 2019-07-31 07:15:24 |
|    7 | 2019-07-31 18:28:11 | 2019-08-01 07:16:47 |
|    7 | 2019-08-01 18:55:05 | 2019-08-02 08:53:49 |
|    7 | 2019-08-02 08:54:06 | 2019-08-03 08:41:31 |
|    7 | 2019-08-03 08:41:44 | 2019-08-04 08:49:47 |
|    7 | 2019-08-04 08:50:04 | 2019-08-04 17:10:13 |
|    7 | 2019-08-05 19:00:52 | 2019-08-06 08:49:46 |
|    7 | 2019-08-06 08:49:57 | 2019-08-07 07:15:56 |
+------+---------------------+---------------------+

关于Mysql选择当天或第二天之间的最新记录，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/57459937/

文章推荐： javascript - 如果支持媒体查询，如何进行基因测试？

scala - Scala(最新 2.10)与 Groovy++(最新 0.9.1？)的比较
就目前情况而言，这个问题不太适合我们的问答形式。我们希望答案得到事实、引用资料或专业知识的支持，但这个问题可能会引发辩论、争论、民意调查或扩展讨论。如果您觉得这个问题可以改进并可能重新开放，visit
pyEcharts安装及详细使用指南(最新)
ECharts是一个纯Javascript的图表库，可以流畅的运行在PC和移动设备上，兼容当前绝大部分浏览器，底层依赖轻量级的Canvas类库ZRender，提供直观、生动、可交互、可高度个性化定制
Docker如何快速搭建LNMP环境(最新)
前言提示：这里可以添加本文要记录的大概内容：例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。提示：以下是本篇文章正文
详解使用Xcode7的Instruments检测解决iOS内存泄露(最新)
作为一名ios开发攻城狮,在苹果没有出arc(自动内存管理机制)时,我们几乎有一半的开发时间都耗费在这么管理内存上.后来苹果很人性的出了arc,虽然在很大程度上,帮助我们开发者节省了精力和时间.但是
pandas - 以相反的顺序使用系列(最新)
如何反转Pandas中DataSeries的排序顺序，以便我按降序使用它们？最佳答案 In [28]: s = pd.Series([20, 10, 30], ['c', 'a', 'b']) In
jQuery 最新 $.browser
这个问题已经有答案了: 已关闭10 年前。 Possible Duplicate: Is jQuery $.browser Deprecated? 最新的 jQuery 库是否发生变化 $.brows
MySQL 更新其他行以跟踪当前/最新
我正在开发一个 Intranet 项目，所以我无法复制/粘贴代码，所以希望我的描述和一些小片段会有所帮助。我知道 MySQL 触发器无法做到这一点，但希望有一种干净的 JPA PrePersist(
mySQL 组，最新
这是数据集: 人员状态日期埃里克 1 1/1/2015 埃里克 2 2/1/2015 埃里克 3 2015 年 3 月 1 日约翰福音 1 3/1/2015 约翰福音 2 2015 年 2 月 1
php - 按日期排序(最新)
现在我正在使用下面的查询按每篇文章的 auto_increment id 排序 mysql_query("SELECT * FROM articles ORDER BY id DESC"); 我想知道
mysql - 最新 - 邮政编码数据库？
已结束。此问题不符合 Stack Overflow guidelines .它目前不接受答案。我们不允许提出有关书籍、工具、软件库等方面的建议的问题。您可以编辑问题，以便用事实和引用来回答它。关闭
mysql - 最新 - 邮政编码数据库？
已结束。此问题不符合 Stack Overflow guidelines .它目前不接受答案。我们不允许提出有关书籍、工具、软件库等方面的建议的问题。您可以编辑问题，以便用事实和引用来回答它。关闭
php - 按日期排序(最新)
现在我正在使用下面的查询按每篇文章的 auto_increment id 排序 mysql_query("SELECT * FROM articles ORDER BY id DESC"); 我想知道
dedeCMS 织梦的日期时间格式大全(最新)
在我们做文章的时候常用一些函数修改来实现自己的页面效果，例如，时间的自定义格式我们常常需要乃至的，搜集了些有关时间格式的CODE，作大家为参考,希望对大家有些帮助列表页list_article
github - 保持 fork 最新
我想将某些东西提交到 github 存储库，但我(显然)没有任何权利这样做。我对那个 repo 做了一个分支，提交了我的更改并提交了一个 pull-request。现在，问题是过了一段时间其他人已经
sql - 仅连接另一个表中每一行的最高(最新)值
我是 SQL 新手，所以现有的答案对我来说有点复杂。我有三张 table : WORKER |id |name |date |... JOB |id |name |salary |accept AP
excel - 粘贴的形状不被视为“最新”形状
我正在自动从PowerPoint和Excel电子表格中生成PowerPoint报表。在粘贴表格之前，我已经完成了整个过程。我使用PPApp.CommandBars.ExecuteMso ("Past
java - Spring kafka消费者不尊重自动偏移重置=最新
我们有 1 个 Kafka 主题和 1 个分区: 从 spring boot kafka 消费者那里看到一个相当奇怪的行为。 Spring kafka消费者在重新启动时总是从主题的开头开始消费。我已
ios - 如何从ALASSET照片库中获取第一张(最新)照片？
通过编程从iOS照片库获取最新照片是否有技巧？我知道我可以按日期搜索，但是我必须每隔一微秒进行一次扫描，以便进行某种比较以准确地找到它。有没有人做过这个或任何想法？最佳答案我之前采取的一种方法
javascript - 最新 Kendo 版本的页面加载缓慢问题
我们上周将 Web 应用程序中的 Telerik Kendo 库从 V2015.2.902 升级到 V2016.1.112。从那时起，我们注意到使用 Kendo 的页面需要很长时间才能加载(30 秒到
sql - 为每个学生选择*最新*成绩信息
我有两个表: STUDENT GRADES ---------- ---------- id id name person_id ad

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

Mysql选择当天或第二天之间的最新记录