问题描述
考虑下面的表结构和示例数据 -
Consider the following table structure and sample data -
EmpID InputDateTime StatusINOUT
-------------------------------------
1 2018-05-26 08:44 1
1 2018-05-26 08:44 2
2 2018-05-28 08:44 1
2 2018-05-28 12:44 2
1 2018-05-21 08:44 1
1 2018-05-21 10:44 2
2 2018-05-23 08:44 1
2 2018-05-23 08:44 2
现在我想将 InputDateTime
列分成两列,即 INTIME(1)
和 OUTTIME(2)
.这背后的逻辑是 StatusInOut
为 1 的日期将是 InTime
而对于 StatusInOut
是 2,日期值将是 OUTTIME(2)
.
Now I want to separate column InputDateTime
into two columns i.e., INTIME(1)
and OUTTIME(2)
. The logic behind this is the date for which StatusInOut
is 1 will be InTime
and for StatusInOut
is 2 that date value will be OUTTIME(2)
.
预期的输出格式如下:
Empid INTIME(1) OUTIME(2)
--------------------------------------------
1 2018-05-26 08:44 2018-05-26 08:44
2 2018-05-28 08:44 2018-05-28 12:44
1 2018-05-21 08:44 2018-05-21 10:44
2 2018-05-23 08:44 2018-05-23 08:44
这是我迄今为止尝试过的
This is what I have tried so far
create table #tempStatus (EmpId int, intTime datetime, sStatus int)
insert into #tempStatus
values(1, '2018-05-26 08:44', 1),
(1, '2018-05-26 08:44', 2),
(2, '2018-05-28 08:44', 1),
(2, '2018-05-28 12:44', 2),
(1, '2018-05-21 08:44', 1),
(1, '2018-05-21 10:44', 2),
(2, '2018-05-23 08:44', 1),
(2, '2018-05-23 08:44', 2)
select EmpId, MIN(intTime) as intTime, MIN(intTime) as OutTime into #tempA from (
select EmpId, intTime, intTime as OutTime
from #tempStatus where sStatus = 1
)a
group by EmpId, intTime
select EmpId, MAX(outTime) as outTime into #tempB from(
select EmpId, intTime as outTime
from #tempStatus where sStatus = 2
)b
group by empId,outTime
select * from #tempA order by EmpId
drop table #tempA
drop table #tempB
DROP TABLE #tempStatus
推荐答案
您需要 row_number()
s &使用它们的差异进行条件聚合,这也称为Gaps and Islands
问题:
You need row_number()
s & use differences of them to do conditional aggregation, This also called as Gaps and Islands
problem :
select empid,
max(case when sStatus = 1 then intTime end) as INTIME,
max(case when sStatus = 2 then intTime end) as OUTIME
from (select t.*,
row_number () over ( order by inttime) as seq1,
row_number () over (partition by empid order by inttime) as seq2
from #tempStatus t
) t
group by empid, (seq1-seq2);
如果你想在 InTime
不存在时显示 OutTime
,那么你可以使用子查询:
EDIT : If you want to display OutTime
whenever InTime
it not there, then you can use subquery :
select t.empid,
coalesce(INTIME, OUTIME) as INTIME,
coalesce(OUTIME, INTIME) as OUTIME
from ( <query here>
) t;
这篇关于如何将数据分成两列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!