如何在 MYSQL 中将行配对在一起？

我正在开发一个简单的时间跟踪应用程序。

我创建了一个表来记录员工的进出时间。

以下是我的数据当前外观的示例：

E_ID | In_Out |      Date_Time
------------------------------------
  3  |   I    | 2012-08-19 15:41:52
  3  |   O    | 2012-08-19 17:30:22
  1  |   I    | 2012-08-19 18:51:11
  3  |   I    | 2012-08-19 18:55:52
  1  |   O    | 2012-08-19 20:41:52
  3  |   O    | 2012-08-19 21:50:30

我正在尝试创建一个查询，将员工的 IN 和 OUT 时间配对到一行中，如下所示：

E_ID |       In_Time       |      Out_Time
------------------------------------------------
  3  | 2012-08-19 15:41:52 | 2012-08-19 17:30:22
  3  | 2012-08-19 18:55:52 | 2012-08-19 21:50:30
  1  | 2012-08-19 18:51:11 | 2012-08-19 20:41:52

我希望我清楚地表达了我想要在这里实现的目标。 基本上我想生成一份将进出时间合并为一行的报告。

任何对此的帮助将不胜感激。 提前致谢。

我能想到三种基本方法。

一种方法使用 MySQL 用户变量，一种方法使用 theta JOIN，另一种方法使用 SELECT 列表中的子查询。

西塔-加入

一种方法是使用 theta-JOIN。这种方法是一种通用的 SQL 方法（没有 MySQL 特定的语法），可以与多个 RDBMS 一起使用。

注意：对于大量行，此方法可能会创建非常大的中间结果集，这可能会导致性能问题。

SELECT o.e_id, MAX(i.date_time) AS in_time, o.date_time AS out_time    
  FROM e `o`
  LEFT
  JOIN e `i` ON i.e_id = o.e_id AND i.date_time < o.date_time AND i.in_out = 'I'
 WHERE o.in_out = 'O'
 GROUP BY o.e_id, o.date_time
 ORDER BY o.date_time

其作用是将员工的每个“O”行与之前的每个“I”行进行匹配，然后我们使用 MAX 聚合来挑选具有最接近日期时间的“I”记录。

这适用于完美配对的数据；可能会为不完美的对产生奇怪的结果...（没有中间“I”行的两个连续“O”记录，都将与相同的“I”行匹配，等等）

SELECT 列表中的相关子查询

另一种方法是在 SELECT 列表中使用相关子查询。这可能具有次优性能，但有时是可行的（并且有时是返回指定结果集的最快方法......当我们在外部查询中返回的行数有限时，此方法效果最佳。）

 SELECT o.e_id
      , (SELECT MAX(i.date_time)
           FROM e `i`
          WHERE i.in_out = 'I'
            AND i.e_id = o.e_id
            AND i.date_time < o.date_time
        ) AS in_time
      , o.date_time AS out_time
   FROM e `o`
  WHERE o.in_out = 'O'
  ORDER BY o.date_time

用户变量

另一种方法是利用 MySQL 用户变量。 （这是 MySQL 特定的方法，是“缺失”分析函数的解决方法。）

此查询的作用是先按 e_id 对所有行进行排序，然后按 date_time 排序，以便我们可以按顺序处理它们。每当我们遇到“O”（出）行时，我们都会使用前一个“I”行中的 date_time 值作为“in_time”）

注意：MySQL 用户变量的这种使用取决于 MySQL 按特定顺序（可预测的计划）执行操作。使用内联视图（或 MySQL 术语中的“派生表”）为我们提供了可预测的执行计划。但这种行为可能会在 MySQL 的未来版本中发生变化。

SELECT c.e_id
     , CAST(c.in_time AS DATETIME) AS in_time
     , c.out_time
  FROM (
         SELECT IF(@prev_e_id = d.e_id,@in_time,@in_time:=NULL) AS reset_in_time
              , @in_time := IF(d.in_out = 'I',d.date_time,@in_time) AS in_time
              , IF(d.in_out = 'O',d.date_time,NULL) AS out_time
              , @prev_e_id := d.e_id  AS e_id
           FROM (
                  SELECT e_id, date_time, in_out 
                    FROM e
                    JOIN (SELECT @prev_e_id := NULL, @in_time := NULL) f
                   ORDER BY e_id, date_time, in_out 
                 ) d
       ) c
 WHERE c.out_time IS NOT NULL
 ORDER BY c.out_time

这适用于您拥有的数据集，它需要更彻底的测试和调整，以确保当行未完美配对时（例如，两个“O”行没有“I”行），您可以使用奇怪的数据获得所需的结果集在它们之间，有一个“I”行，没有后续的“O”行，等等）

SQL小提琴 http://sqlfiddle.com/#!2/84342/8