求SQL語句

[builderwfy]

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
如何實現

[dawugui]

create table tb(person varchar(10) , ttime varchar(10) , Iotype int)
insert into tb values('001', '08:00', 1 )
insert into tb values('001', '12:00', 2 )
insert into tb values('001', '13:00', 1 )
insert into tb values('001', '17:00', 2 )
insert into tb values('002', '08:00', 1 )
insert into tb values('002', '12:00', 2 )
go

select person , 
  max(case (px-1) % 2 when 0 then ttime else '' end ) [in],
  max(case (px-1) % 2 when 1 then ttime else '' end ) [out]
from
(
  select * , px = (select count(1) from tb where person = t.person and ttime < t.ttime) + 1 from tb t
) m
group by person , (px-1)/2
order by person  , [in]

drop table tb

/*
person     in         out        
---------- ---------- ---------- 
001        08:00      12:00
001        13:00      17:00
002        08:00      12:00

（所影响的行数为 3 行）

*/

[dawugui]

create table tb(person varchar(10) , ttime varchar(10) , Iotype int)
insert into tb values('001', '08:00', 1 )
insert into tb values('001', '12:00', 2 )
insert into tb values('001', '13:00', 1 )
insert into tb values('001', '17:00', 2 )
insert into tb values('002', '08:00', 1 )
insert into tb values('002', '12:00', 2 )
go

select person , 
  max(case (px-1) % 2 when 0 then ttime else '' end ) [in],
  max(case (px-1) % 2 when 1 then ttime else '' end ) [out]
from
(
  select * , px = (select count(1) from tb where person = t.person and ttime < t.ttime) + 1 from tb t
) m
group by person , (px-1)/2
order by person  , [in]

/*
person     in         out        
---------- ---------- ---------- 
001        08:00      12:00
001        13:00      17:00
002        08:00      12:00

（所影响的行数为 3 行）

*/



select Wpid = (select count(1) from 
(
select person , 
  max(case (px-1) % 2 when 0 then ttime else '' end ) [in],
  max(case (px-1) % 2 when 1 then ttime else '' end ) [out]
from
(
  select * , px = (select count(1) from tb where person = t.person and ttime < t.ttime) + 1 from tb t
) m
group by person , (px-1)/2
) t1 where t1.person < t2.person or (t1.person = t2.person and t1.[in] < t2.[in])
) + 1 , t2.* from
(
select person , 
  max(case (px-1) % 2 when 0 then ttime else '' end ) [in],
  max(case (px-1) % 2 when 1 then ttime else '' end ) [out]
from
(
  select * , px = (select count(1) from tb where person = t.person and ttime < t.ttime) + 1 from tb t
) m
group by person , (px-1)/2
) t2
order by wpid
/*
Wpid        person     in         out        
----------- ---------- ---------- ---------- 
1           001        08:00      12:00
2           001        13:00      17:00
3           002        08:00      12:00

（所影响的行数为 3 行）
*/

drop table tb