mysql - sql building的进出记录

Question

来自下表:

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

在一个完美的世界里
Tom 通过“DOOR”进入建筑物一次，并通过“DOOR”离开一次。汤姆是完美的!像汤姆一样! :)
Rob 也很完美，但他很困，所以他来下午类。 :P
安娜来和汤姆一起工作。 Tom 一直为她打开“DOOR”，因此没有关于她进入的记录。而且她经常回来，因为她忘记了什么。
约翰是个狡猾的人!他来晚了，应该想办法，但他总是在别人走的时候和别人一起溜进“DOOR”。
最后结婚。她上夜类，所以她需要在一天的两条记录中出现在一张 table 上。

有没有可能在一个表中通过一个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 

查询适用于来自“完美世界”(汤姆和罗布)的人，更多/更少适用于约翰。
不幸的是，查询不适用于 Anna 和 Marry。

PS:对不起我的英语

Answer 1

这是一个棘手的问题，但我想出了一个查询来做到这一点。它使用一些连接、子查询和联合，但会产生您想要的输出。我从 @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子句我使用 JOIN将进入建筑物的记录(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 again 只是输出逻辑:
  • 我们没有进入人员的记录，因此永远不会有时间戳。输入时间返回'-'即可
  • 离开时间戳的处理逻辑同上
• 这次我们从离开记录开始，并将进入记录加入其中。 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