mysql - 子查询返回多条记录

Question

我有一个名为时钟的表，它跟踪员工全天的工作时间。记录的类型为 0、1、2 或 3，即开始移位、开始中断、结束中断、结束移位——按该顺序。员工每天可以多次休息。如何使用 start_time、end_time 和 length 列返回所有中断时间。

表记录示例: Table Records

输出示例: Output Example

这是我当前使用的 SQL 查询，我必须删除第二个中断时间(记录 ID 44 和 45)才能运行它:

SELECT 
@start := (SELECT time as start FROM clock WHERE employeeID = 1 AND time BETWEEN 
                        (SELECT time FROM clock WHERE employeeID = 1 AND type=0 ORDER BY UNIX_TIMESTAMP(time) DESC LIMIT 1) AND CURRENT_TIMESTAMP AND type = 1) as start_time,
@end := (SELECT time as end FROM clock WHERE employeeID = 1 AND time BETWEEN 
                        (SELECT time FROM clock WHERE employeeID = 1 AND type=0 ORDER BY UNIX_TIMESTAMP(time) DESC LIMIT 1) AND CURRENT_TIMESTAMP AND type = 2) as end_time,
                        TIMEDIFF(@end, @start) as length

抱歉，我自己没有 SQL 词汇来 google 这个，我总是在论坛上结束，他们告诉运算符(operator)将子查询的结果限制为 1，我需要他们所有的中断才能返回。

[Edit0:刚刚意识到我的第二张图片有一个误导性的专栏。最后一列应该是长度]

[编辑1:很抱歉链接已损坏，我刚刚创建了帐户，无法发布链接。这是一个 sql fiddle 示例 http://sqlfiddle.com/#!9/bc49e9/1/0 ]

SQLFiddle

Answer 1

为了对连续数据执行所需操作，您需要使用 LAG 分析函数。问题是Mysql还不支持它。所以你必须模仿它的行为。

LAG 函数基本上允许您使用先前的行值(基于顺序和分组)。

要在 MySql 中执行此操作，您必须使用一些变量和子查询。这是您需要的查询:

SELECT t.employeeID, 
       t.starttime, 
       t.endtime, 
       date_format(TIMEDIFF(t.endtime, t.starttime), '%H:%i:%s') as length 
  FROM (SELECT clk.employeeID, 
               clk.`type`,
               @lag AS starttime,
               (@lag := clk.`time`) AS endtime
          FROM (SELECT c.*
                  FROM (SELECT  @_type = NULL,
                                @lag := NULL) vars,
                       clock c
                 ORDER BY c.employeeID, 
                          c.`time`
               ) clk
        WHERE (CASE WHEN @_type IS NULL 
                      OR @_type <> type 
               THEN @lag := NULL 
               ELSE NULL END) IS NULL
          AND (@_type := type) IS NOT NULL
       ) AS t
 WHERE t.`type` = 2
   AND t.starttime IS NOT NULL;

在这里查看它的工作情况:http://sqlfiddle.com/#!9/239b23/18

解释我对外部查询所做的过滤器:

WHERE t.`type` = 2
 AND t.starttime IS NOT NULL;

不带过滤器的查询的行为是获取本例中的 a 值 endtime alias 及其之前的值。如果您不放置此过滤器，则第一行将出现 null, 2016-06-01 16:04:07 作为开始和结束(如果您仅按类型 1,2 进行过滤) ，因此我们取出 start 为 null 的列。

t.type = 2 过滤器将删除不需要的值，因为它是按员工和类型“分组”的。看看如果没有这些过滤器它会如何工作:http://sqlfiddle.com/#!9/239b23/21

请注意，在第二个链接上，我添加了过滤器AND type in (1,2)，由于外部过滤器t.type = 2<，第一个链接不需要该过滤器AND type in (1,2)/