mysql - 合并单个表中的行显示重复结果

Question

我正在开发一个简单的 PHP 和 MySQL 项目，它具有 checkin 和 checkout 功能。

我在合并单个表中的行时遇到了一点问题。下面是您可以在不合并行的情况下看到的原始数据。

mysql> SELECT * FROM clock;
+--------+---------+---------------------+----------+
| log_id | user_id | xtimestamp           | text     |
+--------+---------+---------------------+----------+
|      1 | 1000     | 2012-05-10 07:47:06 | CHECKIN  |
|      2 | 1003     | 2012-05-10 07:47:23 | CHECKIN  |
|      3 | 1002     | 2012-05-10 07:47:36 | CHECKIN  |
|      4 | 1002     | 2012-05-10 07:47:49 | CHECKOUT |
|      5 | 1003     | 2012-05-10 07:48:11 | CHECKOUT |
|      6 | 1000     | 2012-05-10 07:48:23 | CHECKOUT |
|      7 | 1000     | 2012-05-10 07:48:52 | CHECKIN  |
+--------+---------+---------------------+----------+

这是我的 SQL 查询，用于合并单个表上的行以获得 checkin 和 checkout View 。

SELECT a.log_id as 'ID'
 , a.user_id as 'User_ID'
 , a.xtimestamp as 'CHECKIN'
 , b.xtimestamp as 'CHECKOUT' 
FROM clock a, clock b 
WHERE a.info = 'CHECKIN' 
AND b.info = 'CHECKOUT' 
AND a.user_id = b.user_id 
GROUP BY a.log_id;

这是查询的结果。

+----+---------+---------------------+---------------------+
| ID | User_ID | CHECKIN             | CHECKOUT            |
+----+---------+---------------------+---------------------+
|  1 | 1000    | 2012-05-10 07:47:06 | 2012-05-10 07:48:23 |
|  2 | 1003    | 2012-05-10 07:47:23 | 2012-05-10 07:48:11 |
|  3 | 1002    | 2012-05-10 07:47:36 | 2012-05-10 07:47:49 |
|  7 | 1000    | 2012-05-10 07:48:52 | 2012-05-10 07:48:23 |
+----+---------+---------------------+---------------------+

正如您在上面的结果中看到的，返回了 4 条记录，但应该是 3 条，因为在 ID 7 和 user_id 1000 上还没有 CHECKOUT 记录，id 1 和 id 7 具有相同的 CHECKOUT 日期。

我想实现的是这样的

+----+---------+---------------------+---------------------+
| ID | User_ID | CHECKIN             | CHECKOUT            |
+----+---------+---------------------+---------------------+
|  1 | 1000    | 2012-05-10 07:47:06 | 2012-05-10 07:48:23 |
|  2 | 1003    | 2012-05-10 07:47:23 | 2012-05-10 07:48:11 |
|  3 | 1002    | 2012-05-10 07:47:36 | 2012-05-10 07:47:49 |
|  7 | 1000    | 2012-05-10 07:48:52 | null                |
+----+---------+---------------------+---------------------+

或者像这样

+----+---------+---------------------+---------------------+
| ID | User_ID | CHECKIN             | CHECKOUT            |
+----+---------+---------------------+---------------------+
|  1 | 1000    | 2012-05-10 07:47:06 | 2012-05-10 07:48:23 |
|  2 | 1003    | 2012-05-10 07:47:23 | 2012-05-10 07:48:11 |
|  3 | 1002    | 2012-05-10 07:47:36 | 2012-05-10 07:47:49 |
+----+---------+---------------------+---------------------+

如果有人有好主意，请分享..非常感谢。

Answer 1

SELECT   check_in.log_id           AS `CHECKIN ID`
  ,      check_in.user_id          AS `User_ID`
  ,      check_in.xtimestamp       AS `CHECKIN`
  ,      MIN(check_out.xtimestamp) AS `CHECKOUT` 
FROM     clock                     AS `check_in`
  JOIN   clock                     AS `check_out`
      ON (
           check_out.info = 'CHECKOUT'
       AND  check_in.info = 'CHECKIN'
       AND  check_in.user_id     = check_out.user_id
       AND  check_in.xtimestamp <= check_out.xtimestamp
      )
GROUP BY check_in.log_id, check_in.user_id, check_in.xtimestamp;