求SQL語句

builderwfy 2009-03-26 03:43:52

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
如何實現
...全文
65 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2009-03-26
  • 打赏
  • 举报
回复
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
dawugui 2009-03-26
  • 打赏
  • 举报
回复
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 行)

*/

22,211

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧