sql构建的入口和出口记录

6g8kf2rb  于 2021-06-23  发布在  Mysql
关注(0)|答案(4)|浏览(360)

从下表：

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=输入0=输出
我需要收集“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

在一个完美的世界里
汤姆有一次通过“门”进入大楼，然后从“门”离开。汤姆太完美了！像汤姆一样！：）
罗布也很完美，但他是个瞌睡虫，所以他来上下午班p
安娜来和汤姆一起工作。汤姆一直为她开门，所以没有她的入境记录。而且她总是回来，因为她忘了什么。
约翰是道奇！他上班很晚，所以他应该解决这个问题，但当别人离开时，他总是和别人溜出“门”。
终于结婚了。她上夜班，所以她需要在一张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

查询适用于来自“完美世界”（tom&rob）的人，而对john则适用。
不幸的是，这个查询对安娜和玛丽不起作用。
ps：对不起，我的英语不好

sqlmysqlmysql-5.5

4条答案

按热度按时间
jdzmm42g

jdzmm42g1#

SELECT t.Name,t1.TimeStamp asintime,t.timestamp as outtime
FROM tmpAttendance t
LEFT OUTER JOIN tmpAttendance t1 ON t.Name = t1.Name 
                 AND t1.inout = 1
                 AND CAST(t.timestamp AS DATE) =  CAST(t1.timestamp AS DATE)
 WHERE t.inout = 0
 ORDER BY t.Name ,t1.TimeStamp

http://sqlfiddle.com/#!9/df678/5号

赞(0）分享  回复(0）举报  2021-06-23
ddarikpa

ddarikpa2#

玛丽和其他夜班工人
首先让我们谈谈玛丽和其他夜班工人。当我们过滤邮票中的所有内容时，我们可以在邮票中找到它们：
不存在同一天的出戳，即在入戳之后。
但第二天就有一个出局的印章，而第二天就没有一个出局印章之前的出局印章了。
对于出票，我们使用出票次日的00:00。

SELECT d.`timestamp` `timestamp_in`,
       timestampadd(second, -1 * second(d.`timestamp`), timestampadd(minute, -1 * minute(d.`timestamp`), timestampadd(hour, -1 * hour(d.`timestamp`), timestampadd(day, 1, (d.`timestamp`))))) `timestamp_out`,
       d.`name`
       FROM `door` d
       WHERE d.`inout` = 1
             AND NOT EXISTS (SELECT *
                                    FROM `door` di
                                    WHERE di.`name` = d.`name`
                                          AND di.`inout` = 0
                                          AND di.`timestamp` > d.`timestamp`
                                          AND date(di.`timestamp`) = date(d.`timestamp`))
             AND EXISTS (SELECT *
                                FROM `door` di
                                WHERE di.`name` = d.`name`
                                      AND di.`inout` = 0
                                      AND date(di.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                      AND NOT EXISTS (SELECT *
                                                             FROM `door` dii
                                                             WHERE dii.`name` = di.`name`
                                                                   AND date(dii.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                                                   AND dii.`timestamp` < di.`timestamp`
                                                                   AND dii.`inout` = 1))

我们可以用同样的逻辑，把所有东西都调换一下，这样就可以去掉夜班工人的印记了： > 变成 < 日期比较，反之亦然。
哪里有 0 为了一张邮票 1 一张邮票，反之亦然。
与前一天相比，而不是第二天。
对于印内邮票，我们使用印外邮票当天的00:00。
如果我们 UNION ALL 两个问题，我们都得到了夜班的角色。
帮派的其他人也叫日班工人
现在为日班工人准备入场券很容易。我们只需要否定我们用来寻找夜班工人的条件。要获得注册邮票：

SELECT d.`timestamp`,
       d.`name`
       FROM `door` d
       WHERE d.`inout` = 1
             AND (EXISTS (SELECT *
                                 FROM `door` di
                                 WHERE di.`name` = d.`name`
                                       AND di.`inout` = 0
                                       AND di.`timestamp` > d.`timestamp`
                                       AND date(di.`timestamp`) = date(d.`timestamp`))
                   OR NOT EXISTS (SELECT *
                                         FROM `door` di
                                         WHERE di.`name` = d.`name`
                                               AND di.`inout` = 0
                                               AND date(di.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                               AND NOT EXISTS (SELECT *
                                                                      FROM `door` dii
                                                                      WHERE dii.`name` = di.`name`
                                                                            AND date(dii.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                                                            AND dii.`timestamp` < di.`timestamp`
                                                                            AND dii.`inout` = 1)))

对于out stamps，我们可以再次切换， > 变成 < 等等，如上所述。
当然，这里我们不能为out stamp生成in stamp，反之亦然。我们必须这么做 FULL OUTER JOIN 日班工人的进出票。但mysql至少在较低版本中不支持此操作。所以我们就这样做 UNION 第二组 LEFT JOIN ，在第二个 LEFT JOIN 与第一次相比。
把它们放在一起
现在我们可以简单地 UNION ALL 夜班工人和白班工人要得到全天的完整结果。我们可以 SELECT FROM 为了得到某一天的结果：

SELECT coalesce(x.`timestamp_in`, '-') `d1-in-timestamp`,
       coalesce(x.`timestamp_out`, '-') `d0-out-timestamp`,
       x.`name`
       FROM (SELECT r.`timestamp` `timestamp_in`,
                    s.`timestamp` `timestamp_out`,
                    r.`name`
                    FROM (SELECT d.`timestamp`,
                                 d.`name`
                                 FROM `door` d
                                 WHERE d.`inout` = 1
                                       AND (EXISTS (SELECT *
                                                           FROM `door` di
                                                           WHERE di.`name` = d.`name`
                                                                 AND di.`inout` = 0
                                                                 AND di.`timestamp` > d.`timestamp`
                                                                 AND date(di.`timestamp`) = date(d.`timestamp`))
                                             OR NOT EXISTS (SELECT *
                                                                   FROM `door` di
                                                                   WHERE di.`name` = d.`name`
                                                                         AND di.`inout` = 0
                                                                         AND date(di.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                                                         AND NOT EXISTS (SELECT *
                                                                                                FROM `door` dii
                                                                                                WHERE dii.`name` = di.`name`
                                                                                                      AND date(dii.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                                                                                      AND dii.`timestamp` < di.`timestamp`
                                                                                                      AND dii.`inout` = 1)))) r
                         LEFT JOIN (SELECT d.`timestamp`,
                                           d.`name`
                                           FROM `door` d
                                           WHERE d.`inout` = 0
                                                 AND (EXISTS (SELECT *
                                                                     FROM `door` di
                                                                     WHERE di.`name` = d.`name`
                                                                           AND di.`inout` = 1
                                                                           AND di.`timestamp` < d.`timestamp`
                                                                           AND date(di.`timestamp`) = date(d.`timestamp`))
                                                       OR NOT EXISTS (SELECT *
                                                                             FROM `door` di
                                                                             WHERE di.`name` = d.`name`
                                                                                   AND di.`inout` = 1
                                                                                   AND date(di.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL -1 DAY)
                                                                                   AND NOT EXISTS (SELECT *
                                                                                                          FROM `door` dii
                                                                                                          WHERE dii.`name` = di.`name`
                                                                                                                AND date(dii.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL -1 DAY)
                                                                                                                AND dii.`timestamp` > di.`timestamp`
                                                                                                                AND dii.`inout` = 0)))) s
                                   ON s.`name` = r.`name`
                                      AND date(s.`timestamp`) = date(r.`timestamp`)
                                      AND s.`timestamp` > r.`timestamp`
             UNION
             SELECT u.`timestamp` `timestamp_in`,
                    t.`timestamp` `timestamp_out`,
                    t.`name`
                    FROM (SELECT d.`timestamp`,
                                 d.`name`
                                 FROM `door` d
                                 WHERE d.`inout` = 0
                                       AND (EXISTS (SELECT *
                                                           FROM `door` di
                                                           WHERE di.`name` = d.`name`
                                                                 AND di.`inout` = 1
                                                                 AND di.`timestamp` < d.`timestamp`
                                                                 AND date(di.`timestamp`) = date(d.`timestamp`))
                                             OR NOT EXISTS (SELECT *
                                                                   FROM `door` di
                                                                   WHERE di.`name` = d.`name`
                                                                         AND di.`inout` = 1
                                                                         AND date(di.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL -1 DAY)
                                                                         AND NOT EXISTS (SELECT *
                                                                                                FROM `door` dii
                                                                                                WHERE dii.`name` = di.`name`
                                                                                                      AND date(dii.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL -1 DAY)
                                                                                                      AND dii.`timestamp` > di.`timestamp`
                                                                                                      AND dii.`inout` = 0)))) t
                         LEFT JOIN (SELECT d.`timestamp`,
                                           d.`name`
                                           FROM `door` d
                                           WHERE d.`inout` = 1
                                                 AND (EXISTS (SELECT *
                                                                     FROM `door` di
                                                                     WHERE di.`name` = d.`name`
                                                                           AND di.`inout` = 0
                                                                           AND di.`timestamp` > d.`timestamp`
                                                                           AND date(di.`timestamp`) = date(d.`timestamp`))
                                                       OR NOT EXISTS (SELECT *
                                                                             FROM `door` di
                                                                             WHERE di.`name` = d.`name`
                                                                                   AND di.`inout` = 0
                                                                                   AND date(di.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                                                                   AND NOT EXISTS (SELECT *
                                                                                                          FROM `door` dii
                                                                                                          WHERE dii.`name` = di.`name`
                                                                                                                AND date(dii.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                                                                                                AND dii.`timestamp` < di.`timestamp`
                                                                                                                AND dii.`inout` = 1)))) u
                                   ON u.`name` = t.`name`
                                      AND date(u.`timestamp`) = date(t.`timestamp`)
                                      AND u.`timestamp` < t.`timestamp`
             UNION
             SELECT d.`timestamp` `timestamp_in`,
                    timestampadd(second, -1 * second(d.`timestamp`), timestampadd(minute, -1 * minute(d.`timestamp`), timestampadd(hour, -1 * hour(d.`timestamp`), timestampadd(day, 1, (d.`timestamp`))))) `timestamp_out`,
                    d.`name`
                    FROM `door` d
                    WHERE d.`inout` = 1
                          AND NOT EXISTS (SELECT *
                                                 FROM `door` di
                                                 WHERE di.`name` = d.`name`
                                                       AND di.`inout` = 0
                                                       AND di.`timestamp` > d.`timestamp`
                                                       AND date(di.`timestamp`) = date(d.`timestamp`))
                          AND EXISTS (SELECT *
                                             FROM `door` di
                                             WHERE di.`name` = d.`name`
                                                   AND di.`inout` = 0
                                                   AND date(di.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                                   AND NOT EXISTS (SELECT *
                                                                          FROM `door` dii
                                                                          WHERE dii.`name` = di.`name`
                                                                                AND date(dii.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL 1 DAY)
                                                                                AND dii.`timestamp` < di.`timestamp`
                                                                                AND dii.`inout` = 1))
             UNION ALL
             SELECT timestampadd(second, -1 * second(d.`timestamp`), timestampadd(minute, -1 * minute(d.`timestamp`), timestampadd(hour, -1 * hour(d.`timestamp`), d.`timestamp`))) `timestamp_in`,
                    d.`timestamp` `timestamp_out`,
                    d.`name`
                    FROM `door` d
                    WHERE d.`inout` = 0
                          AND NOT EXISTS (SELECT *
                                                 FROM `door` di
                                                 WHERE di.`name` = d.`name`
                                                       AND di.`inout` = 1
                                                       AND di.`timestamp` < d.`timestamp`
                                                       AND date(di.`timestamp`) = date(d.`timestamp`))
                          AND EXISTS (SELECT *
                                             FROM `door` di
                                             WHERE di.`name` = d.`name`
                                                   AND di.`inout` = 1
                                                   AND date(di.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL -1 DAY)
                                                   AND NOT EXISTS (SELECT *
                                                                          FROM `door` dii
                                                                          WHERE dii.`name` = di.`name`
                                                                                AND date(dii.`timestamp`) = date_add(date(d.`timestamp`), INTERVAL -1 DAY)
                                                                                AND dii.`timestamp` > di.`timestamp`
                                                                                AND dii.`inout` = 0))) x
       WHERE date(x.`timestamp_in`) = '2018-04-02'
              OR date(x.`timestamp_out`) = '2018-04-02'
       ORDER BY x.`name`,
                x.`timestamp_in`;

db<>小提琴

赞(0）分享  回复(0）举报  2021-06-23
byqmnocz

byqmnocz3#

只是一个简单的补充：为mary添加一行max time（2018-04-02 23:59）http://sqlfiddle.com/#!9/bb41b1/6号楼
然后你可以使用下面的逻辑：

select a.name,
    case when a.name != 'Mary' and (b.out_time is null or a.in_time <= b.out_time) then a.in_time
         when a.name != 'Mary' and b.out_time is not null and a.in_time > b.out_time then null
         when a.name = 'Mary' and  a.in_time > b.out_time then '2018-04-02 00:00:00'
         else a.in_time
    end as in_time,
    b.out_time     
    from
    (select name,time1 as in_time  from have1
    where inout1 = 1 and (substring(time1,1,10)='2018-04-02')) a
    left join
    (select name,time1 as out_time  from have1
    where inout1 = 0 and (substring(time1,1,10)='2018-04-02')) b
    on a.name = b.name

输出：

name    in_time                 out_time
Tom     2018-04-02 06:00:00  2018-04-02 14:00:00
Rob     2018-04-02 14:00:00  2018-04-02 22:00:00
John    2018-04-02 06:05:00  (null)
Anna    (null)               2018-04-02 14:10:00
Anna    2018-04-02 14:15:00  2018-04-02 14:20:00
Mary    2018-04-02 00:00:00  2018-04-02 06:00:00
Mary    2018-04-02 22:00:00  2018-04-02 23:59:59

如果需要澄清，请告诉我。

赞(0）分享  回复(0）举报  2021-06-23
i86rm4rw

i86rm4rw4#

这是一个困难的问题，但我想出了一个查询来做到这一点。它使用一些连接、子查询和联合，但会生成所需的输出。我从@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 同样，这只是输出逻辑：
我们没有记录的人进入，因此将永远不会有一个时间戳。输入时间返回“-”
处理离开时间戳的逻辑与上述相同
这次我们从离开记录开始，把进入的记录加入到记录中。这个 ON 条款包括以下内容：
使用 name 只加入一个人的记录 outA.inout = 1 因为联接表应该只使用
进入的记录应该发生在建筑物被留下之前( inA.timestamp < outA.timestamp )
如上所述，双方之间可能没有任何其他记录
这个 WHERE 条款又做了一些重要的限制： outA.inout = 0 因为我们需要一张table，上面只有离开大楼的记录 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号

赞(0）分享  回复(0）举报  2021-06-23
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com