时间戳

首页 > 解决方案 > sql楼的进出记录

mysql - sql楼的进出记录

问题描述

从下表：

timestamp         inout  Name
2018-04-01 14:00    0    Tom
2018-04-02 06:00    1    Tom
2018-04-02 14:00    0    Tom
2018-04-03 06:00    1    Tom
2018-04-01 22:00    0    Rob
2018-04-02 14:00    1    Rob
2018-04-02 22:00    0    Rob
2018-04-03 13:00    1    Rob
2018-04-01 12:55    0    John
2018-04-02 06:05    1    John
2018-04-03 06:10    1    John
2018-04-01 14:05    0    Anna
2018-04-02 14:10    0    Anna
2018-04-02 14:15    1    Anna
2018-04-02 14:20    0    Anna
2018-04-03 14:05    0    Anna
2018-04-01 22:00    1    Mary
2018-04-02 06:00    0    Mary
2018-04-02 22:00    1    Mary
2018-04-03 06:00    0    Mary

其中 1=in 0=out
我需要收集“2018-04-02 的进出记录”的数据并将其呈现在如下表格中：

d1-in-timestamp   d0-out-timestamp  Name
2018-04-02 07:00  2018-04-02 15:00  Tom
2018-04-02 14:00  2018-04-02 22:00  Rob
2018-04-02 06:05  -                 John
-                 2018-04-02 14:10  Anna
2018-04-02 14:15  2018-04-02 14:20  Anna
2018-04-02 00:00  2018-04-02 06:00  Mary
2018-04-02 22:00  2018-04-02 00:00  Mary

在一个完美的世界
中，汤姆通过“DOOR”进入大楼一次，然后通过“DOOR”离开一次。汤姆是完美的！像汤姆一样！:)
Rob 也很完美，但他很嗜睡，所以他上下午班。:P
Anna 来和 Tom 一起工作。汤姆一直为她打开“门”，因此没有关于她进入的记录。此外，她不断回来，因为她忘记了一些东西。
约翰很狡猾！他来晚了，所以他应该解决它，但是当别人离开时，他总是和别人一起溜进“门”。
最后结婚。她在上夜班，因此需要在一张桌子上看到她一天分成两份记录。

是否有可能通过一个 SQL 查询在一张表中获得这样的结果？

到目前为止，我管理这样的 sql 查询：

select timestamp as d1, (select timestamp from DOOR where timestamp>m1.timestamp and inout=0 and name=m1.name) as d0, name from DOOR as m1 where substring(timestamp,1,10)='2018-04-02' and inout=1 order by name, timestamp

查询适用于“完美世界”（Tom & Rob）的人，更多/更少适用于约翰。
不幸的是，查询不适用于 Anna 和 Marry。

PS：对不起我的英语

标签： mysqlsqlmysql-5.5

解决方案

这是一个艰难的问题，但我想出了一个查询来做到这一点。它使用一些连接、子查询和联合，但会产生您想要的输出。我从@RajatJaiswals fiddle 开始，但创建了一个全新的查询。

SELECT * FROM (
  SELECT
    IF(inA.timestamp < '2018-04-02', '2018-04-02 00:00:00', inA.timestamp) AS `d1-in-timestamp`, 
    IFNULL(IF(outA.timestamp > '2018-04-02 23:59:59', CAST(DATE_ADD('2018-04-02', INTERVAL 1 DAY) AS DATETIME), outA.timestamp), '-') AS `d0-out-timestamp`, 
    inA.name AS `Name`
  FROM 
    attendance AS inA
    LEFT JOIN attendance AS outA ON (
      inA.name = outA.name 
      AND outA.inout = 0
      AND inA.timestamp < outA.timestamp
      AND NOT EXISTS(
        SELECT betweenA.name 
        FROM attendance AS betweenA
        WHERE 
          betweenA.name = inA.name
          AND betweenA.timestamp > inA.timestamp
          AND betweenA.timestamp < outA.timestamp
      )
    )
  WHERE 
    inA.inout = 1 
    AND (
      CAST(inA.timestamp AS DATE) = '2018-04-02' 
      OR CAST(outA.timestamp AS DATE) = '2018-04-02'
    )

  UNION

  SELECT 
    '-' AS `d1-in-timestamp`, 
    IF(outA.timestamp > '2018-04-02 23:59:59', CAST(DATE_ADD('2018-04-02', INTERVAL 1 DAY) AS DATETIME), outA.timestamp) AS `d0-out-timestamp`, 
    outA.name AS `Name`
  FROM
    attendance AS outA
    LEFT JOIN attendance AS inA ON (
      inA.name = outA.name 
      AND inA.inout = 1
      AND inA.timestamp < outA.timestamp
      AND NOT EXISTS(
        SELECT betweenA.name 
        FROM attendance AS betweenA
        WHERE 
          betweenA.name = inA.name
          AND betweenA.timestamp > inA.timestamp
          AND betweenA.timestamp < outA.timestamp
      )
    )
  WHERE 
    outA.inout = 0
    AND  CAST(outA.timestamp AS DATE) = '2018-04-02'
    AND inA.name IS NULL
) AS a
ORDER BY `Name`, `d1-in-timestamp`

这是一个复杂的查询，起初可能看起来令人生畏，但我尝试将其分解为小部分来解释它的作用：

外部SELECT只是为了对完整结果进行排序。由于该UNION声明，这是必需的。

  • 第一个内部SELECT子句只处理一些输出转换
    • IF(inA.timestamp < '2018-04-02', '2018-04-02 00:00:00', inA.timestamp)仅用于格式化并将前一天的时间戳替换为相关日期的 00:00
    • IFNULL(IF(outA.timestamp > '2018-04-02 23:59:59', CAST(DATE_ADD('2018-04-02', INTERVAL 1 DAY) AS DATETIME), outA.timestamp), '-')再次用于格式化，但做了两件事：-如果该人没有离开建筑物，则将 null 替换为，并将后天的时间戳替换为后天的 00:00

  • 在FROM子句中，我使用 aJOIN将进入建筑物inA的记录 ( ) 与离开建筑物的记录( ) 连接在一起outA。有趣的部分是ON子句：

    • 我使用该name列仅加入同一个人的记录
    • outA表应该只查看离开的人 ( inout = 0)
    • inA.timestamp < outA.timestamp如果此人在离开之前没有进入，则不应将这两个条目连接在一起

    • 连接的两条记录之间不应有任何活动记录。这由NOT EXISTS子查询处理。它搜索任何记录

      • 属于同一个人 ( betweenA.name = inA.name)
      • 发生在有inA问题的记录之后
      • 发生在相关outA记录之前

      如果存在任何此类记录，则该NOT EXISTS子句评估为 false 并且不连接记录。这样，只有随后的输入和输出条目被连接在一起。

  • WHERE子句很简单 ：
    • 确保只选择进入大楼的人员inA
    • 至少有一个条目必须来自所需的日期（CAST(inA.timestamp AS DATE)将时间戳转换为日期，从而删除时间部分并使比较简单）

这将选择记录进入建筑物的人员的所有记录。我们现在仍然怀念没有记录进入大楼的安娜的案例。这就是UNION出现的地方并将此信息添加到结果中。

  • SELECT再次只是输出逻辑 ：
    • 我们没有进入的人的记录，因此永远不会有时间戳。只需返回“-”即可进入时间
    • 离开时间戳的处理逻辑同上
  • 这次我们从留下记录开始，将输入的记录加入到它们中。该ON子句执行以下操作：
    • 用于name仅加入一个人的记录
    • outA.inout = 1因为连接表应该只使用输入记录
    • 进入记录应发生在建筑物离开之前（inA.timestamp < outA.timestamp）
    • 如上所述，之间可能没有任何其他记录
  • 该WHERE子句再次做了一些重要的限制：
    • outA.inout = 0因为我们需要将表格限制为离开建筑物的记录
    • CAST(outA.timestamp AS DATE) = '2018-04-02'这次只检查日期，outA因为没有进入记录。
    • 只使用没有找到进入记录的结果（即没有找到加入记录的地方）。如果是这种情况inA.name IS NULL

最后一件事是ORDER BY应该是不言自明的条款。

输出：

|     d1-in-timestamp |    d0-out-timestamp | Name |
|---------------------|---------------------|------|
|                   - | 2018-04-02 14:10:00 | Anna |
| 2018-04-02 14:15:00 | 2018-04-02 14:20:00 | Anna |
| 2018-04-02 06:05:00 |                   - | John |
| 2018-04-02 00:00:00 | 2018-04-02 06:00:00 | Mary |
| 2018-04-02 22:00:00 | 2018-04-03 00:00:00 | Mary |
| 2018-04-02 14:00:00 | 2018-04-02 22:00:00 |  Rob |
| 2018-04-02 06:00:00 | 2018-04-02 14:00:00 |  Tom |

您可以在以下 SQL Fiddle 中尝试：http ://sqlfiddle.com/#!9/e618bb/7/0

推荐阅读