mysql - 在 MySQL 上排列数据

Question

有什么办法可以安排这种输出吗？

我希望日期时间和状态在另一列上分开

输出应该是这样的：

company_id | 姓名 | 日期时间 C/In | 状态 C/In | 日期时间 C/Out | 状态 C/Out

谢谢

标签： mysql

Answer 1

假设没有人在午夜工作，您可以按公司、员工、日期汇总，使用工会捕获任何已退出但未登录的人。例如

drop table if exists t;
create table t 
(company int, employee int, dt datetime , state varchar(5));

insert into t values
(1,1,'2020-03-01 08:00:01','cin'),
(1,1,'2020-03-01 08:00:02','cin'),
(1,1,'2020-03-01 08:00:03','cout'),
(1,1,'2020-03-02 08:00:01','cin'),
(1,1,'2020-03-02 08:00:02','cout'),
(1,1,'2020-03-02 08:00:03','cout'),
(1,1,'2020-03-03 08:00:01','cin'),
(1,1,'2020-03-04 08:00:01','cout')
;

select company,employee,'cin', min(dt) cin,'cout',
         (select max(dt) 
         from t t1 
         where t1.company = t.company and 
                 t1.employee = t.employee and
                 date(t1.dt) = date(t.dt) and 
                 state = 'cout') cout
from t
where state = 'cin'
group by company,employee,date(dt)

union all

select company,employee,'cin', null cin,
         'cout', max(dt)
from t
where state = 'cout' and 
        (select min(dt) 
         from t t1 
         where t1.company = t.company and 
                 t1.employee = t.employee and
                 date(t1.dt) = date(t.dt) and 
                 state = 'cin') is null
group by company,employee,date(dt);

+---------+----------+-----+---------------------+------+---------------------+
| company | employee | cin | cin                 | cout | cout                |
+---------+----------+-----+---------------------+------+---------------------+
|       1 |        1 | cin | 2020-03-01 08:00:01 | cout | 2020-03-01 08:00:03 |
|       1 |        1 | cin | 2020-03-02 08:00:01 | cout | 2020-03-02 08:00:03 |
|       1 |        1 | cin | 2020-03-03 08:00:01 | cout | NULL                |
|       1 |        1 | cin | NULL                | cout | 2020-03-04 08:00:01 |
+---------+----------+-----+---------------------+------+---------------------+
4 rows in set (0.002 sec)