MySQL:将结果分成两个不同的列

Question

我的结果如下

DATE        EID TIME        TYPE

2015-07-26  1   10:01:00    IN 
2015-07-26  1   15:01:00    OUT 
2015-07-26  1   18:33:00    IN 
2015-07-26  1   23:11:00    OUT

我想将 IN、OUT 分成不同的列 ORDER BY date, eid, time。预期结果应该如下

DATE        EID     IN TIME     OUT TIME

2015-07-26  1       10:01:00    15:01:00
2015-07-26  1       18:33:00    23:11:00

这是我目前尝试过的

SELECT `date` AS 'DATE', `eid` AS 'EID',
CASE WHEN `type` = 'IN' THEN `time` END AS 'IN TIME',
CASE WHEN `type` = 'OUT' THEN `time` END AS 'OUT TIME'
FROM `attendance`
ORDER BY `date`, `eid`, `time`;

它正在获取一些荒谬的结果，如下所示

DATE        EID     IN TIME     OUT TIME

2015-07-26  1       10:01:00    null
2015-07-26  1       null        15:01:00
2015-07-26  1       18:33:00    null
2015-07-26  1       null        23:11:00

更新:

这是我的表结构

Field       Type                Null    Key     Default     Extra

id      int(10) unsigned        NO      PRI     NULL        auto_increment
eid     int(10) unsigned        NO              NULL
time    time                    NO              00:00:00
date    date                    NO              0000-00-00
type    enum('IN', 'OUT')       NO              NULL
state   tinyint(1) unsigned     NO              1

这里有更多元组...

DATE        EID TIME        TYPE

2015-07-26  1   10:01:00    IN
2015-07-26  1   15:01:00    OUT
2015-07-26  1   18:33:00    IN
2015-07-26  1   23:11:00    OUT
2015-07-26  3   09:42:00    IN
2015-07-26  3   15:29:00    OUT
2015-07-26  3   18:20:00    IN
2015-07-26  3   00:34:00    OUT
2015-07-26  6   14:16:00    IN
2015-07-26  6   23:08:00    OUT
2015-07-26  8   13:32:00    IN
2015-07-26  8   23:57:00    OUT
2015-07-26  12  09:14:00    IN
2015-07-26  12  15:07:00    OUT
2015-07-26  12  17:28:00    IN
2015-07-26  12  23:53:00    OUT
2015-07-26  13  13:47:00    IN
2015-07-26  13  23:25:00    OUT
2015-07-26  15  11:07:00    IN
2015-07-26  15  19:50:00    OUT

Answer 1

我已经为你写了一个查询。我希望它能解决您的问题:

SQL FOR TABLE SCHEMA

CREATE TABLE `attendance` (
  `date` date DEFAULT NULL,
  `eid` int(11) DEFAULT NULL,
  `time` time(6) DEFAULT NULL,
  `type` varchar(5) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

SQL 查询结果

我假设您的表中有一个自动递增列作为 id

SELECT * FROM (
    SELECT a.`date` AS 'DATE', a.`eid` AS 'EID',
        CASE WHEN `type` = 'IN' THEN a.`time` END AS 'IN TIME',
        CASE WHEN `type` = 'IN' THEN 
            (select `time` from `attendance` where id > a.id AND `type` = 'OUT' LIMIT 1) END AS 'OUT TIME'
        FROM `attendance` a
        ORDER BY a.`date`, a.`eid`, a.`time`
) as t WHERE t.`IN TIME` IS NOT NULL;