SQL语句将下表test
person ttime Iotype
001 8:00 1
001 12:00 2
001 13:00 1
001 17:00 2
002 8:00 1
002 12:00 2
转换成表
Wpid person in Out
1 001 8:00 12:00
2 001 13:00 17:00
3 002 8:00 12:00
person ttime Iotype
001 8:00 1
001 12:00 2
001 13:00 1
001 17:00 2
002 8:00 1
002 12:00 2
转换成表
Wpid person in Out
1 001 8:00 12:00
2 001 13:00 17:00
3 002 8:00 12:00
/*
Wpid person in Out
1 001 8:00 12:00
2 001 13:00 17:00
3 002 8:00 12:00
*/select person ,Iotype
max(case when ttime in ('8:00','13:00') then ttime end )as 'in',
max(case when ttime in ('12:00','17:00') then ttime end )as 'out'
from t
group by person ,Iotype
--如果你的數據長的好,即都是1,2輪流出現,處理比較方便create table Test(person varchar(10),ttime datetime,Iotype int)
insert into Test values('001','8:00',1)
insert into Test values('001','12:00',2)
insert into Test values('001','13:00',1)
insert into Test values('001','17:00',2)
insert into Test values('002','8:00',1)
insert into Test values('002','12:00',2)
select id=identity(int,1,1) ,* into # from testselect tmp=(select count(*) from # where id<=a.id and iotype=1),
person,
convert(char(05),ttime,108) as [in],
[out]=(select convert(char(05),ttime,108) from # where id=a.id+1)
from # A
where iotype=1
/*tmp person in out
----------- ---------- ----- -----
1 001 08:00 12:00
2 001 13:00 17:00
3 002 08:00 12:00
*/
drop table Test,#
select wpid = identity( int , 1 , 1 ) ,
a.person ,
a.in ,
b.out from(
select person , in = ttime
from test
where iotype = 1
)
as a left join (
select person , out = ttime
from test
where iotype = 2
)
as b on a.person = b.person into #tmp
insert into Test values('001','8:00',1)
insert into Test values('001','12:00',2)
insert into Test values('001','13:00',1)
insert into Test values('001','17:00',2)
insert into Test values('002','8:00',1)
insert into Test values('002','12:00',2)goselect
person,
[in]=convert(varchar(5),ttime,108),
[out]=(select top 1 convert(varchar(5),ttime,108) from test where Iotype=2 and ttime>t.ttime order by ttime asc)
from
test t
where
Iotype=1
person in out
---------- ----- -----
001 08:00 12:00
001 13:00 17:00
002 08:00 12:00(所影响的行数为 3 行)
go
create table Test(person varchar(10),ttime datetime,Iotype int)
insert into Test values('001','8:00',1)
insert into Test values('001','12:00',2)
insert into Test values('001','13:00',1)
insert into Test values('001','17:00',2)
insert into Test values('002','8:00',1)
insert into Test values('002','12:00',2)go
--加条件person
select
person,
[in]=convert(varchar(5),ttime,108),
[out]=(select top 1 convert(varchar(5),ttime,108) from test where person=t.person and Iotype=2 and ttime>t.ttime order by ttime asc)
from
test t
where
Iotype=1
或:
select
person,
[in]=convert(varchar(5),ttime,108),
[out]=(select min( convert(varchar(5),ttime,108)) from test where person=t.person and Iotype=2 and ttime>t.ttime )
from
test t
where
Iotype=1
/*
person in out
---------- ----- -----
001 08:00 12:00
001 13:00 17:00
002 08:00 12:00(所影响的行数为 3 行)*/