思考一天毫无结果!!!!!求高人赐教

wangxuelid 2007-12-18 08:25:57
2、请写SQL语句将下表test1
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

转换成表test2
Wpid person in Out
1 001 8:00 12:00
2 001 13:00 17:00
3 002 8:00 12:00
最好使用一句SQL语句搞顶(不要使用变量或函数,游标之类),,,不胜感激
...全文
128 16 打赏 收藏 举报
写回复
16 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
ljking0731 2007-12-18
根据tim_spac的语句,优化了一下
--建表
create table #Table (person varchar(5),ttime varchar(6),IOType int)
insert into #Table
select '001', '8:00', 1 union all
select '001', '12:00', 2 union all
select '001', '13:00', 1 union all
select '001', '17:00', 2 union all
select '002', '8:00', 1 union all
select '002', '12:00', 2

--换成时间格式
update #table Set ttime=substring(convert(char(21),cast(ttime as datetime),21),12,5)
--查询
select person,ttime as [in],(select min(ttime) from #Table where person=a.person and IoType=2 and ttime>a.ttime) as [out]
from #table as a
where Iotype=1
--结果
person in out
------ ------ ------
001 08:00 12:00
001 13:00 17:00
002 08:00 12:00

(所影响的行数为 3 行)
  • 打赏
  • 举报
回复
ljking0731 2007-12-18
--建表
create table #Table (person varchar(5),ttime varchar(6),IOType int)
insert into #Table
select '001', '8:00', 1 union all
select '001', '12:00', 2 union all
select '001', '13:00', 1 union all
select '001', '17:00', 2 union all
select '002', '8:00', 1 union all
select '002', '12:00', 2

--换成时间格式
update #table Set ttime=substring(convert(char(21),cast(ttime as datetime),21),12,5)
--查询
select distinct * from
(
select person,
case when IoType=1 then ttime else (select max(ttime) from #table where person=a.person and IoType=1 and ttime<a.ttime) end as [in],
case when IoType=2 then ttime else (select min(ttime) from #Table where person=a.person and IoType=2 and ttime>a.ttime) end as [out]
from #table as a
) as a
--结果
person in out
------ ------ ------
001 08:00 12:00
001 13:00 17:00
002 08:00 12:00

(所影响的行数为 3 行)
  • 打赏
  • 举报
回复
tim_spac 2007-12-18
对大家的代码略作调整如下:

DECLARE @T TABLE (person varchar(10), ttime varchar(10), iotype int)
insert into @t
select '001' ,'8:00',1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'8:00',1 union all
select '002' ,'12:50',2

select person, inTime = ttime,
(
select min(b.ttime)
from @t as b
where 1=1
and b.person=a.person
and b.iotype=2
and b.person=a.person
and convert(datetime,b.ttime) > convert(datetime,a.ttime)
) as ttime2
from @t as a
where a.iotype=1
  • 打赏
  • 举报
回复
wangxuelid 2007-12-18
声明: 先感谢大家,来着都有分,,,
  • 打赏
  • 举报
回复
wangxuelid 2007-12-18
DECLARE @T TABLE (person varchar(10), ttime varchar(10),Iotype int)
insert into @t
select '001' , '8:00' ,1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'8:00', 1 union all
select '002' ,'12:00',2
select t.person,t.ttime as [in],tt.ttime as [out]
from (select person ,ttime , case ttime when '8:00' then 1 else 2 end Iotype from @t where Iotype=1) t inner join (
select person ,ttime , case ttime when '12:00' then 1 else 2 end Iotype from @t where Iotype=2)tt on t.person=tt.person and t.Iotype=tt.Iotype
//结果
001 8:00 12:00
001 13:00 17:00
002 8:00 12:00
  • 打赏
  • 举报
回复
conqweal 2007-12-18
DECLARE @T TABLE (person varchar(10), ttime varchar(10),Iotype int)
insert into @t
select '001' , '8:00' ,1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'8:00', 1 union all
select '002' ,'12:00',2


select a.*,(select top 1 b.ttime from @t as b where 1=1 and b.person=a.person and b.iotype=2 and b.person=a.person and cast(replace(b.ttime,':00','') as int)>cast(replace(a.ttime,':00','') as int) ) as ttime2 from @t as a where a.iotype=1

这个是我的最后答案..希望楼主喜欢。
  • 打赏
  • 举报
回复
conqweal 2007-12-18
DECLARE @T TABLE (person varchar(10), ttime varchar(10),Iotype int)
insert into @t
select '001' , '8:00' ,1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'8:00', 1 union all
select '002' ,'12:00',2
select a.*,(select top 1 b.ttime from @t as b where a.person=b.person and cast(replace(b.ttime,':00','') as int)> cast(replace(a.ttime,':00','') as int) ) as ttime2 from @t as a where a.ttime <> '12:00 ' and a.ttime <> '17:00'
  • 打赏
  • 举报
回复
conqweal 2007-12-18
DECLARE @T TABLE (person varchar(10), ttime varchar(10),Iotype int)
insert into @t
select '001' , '8:00' ,1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'8:00', 1 union all
select '002' ,'12:00',2
select a.*,(select top 1 b.ttime from @t as b where a.person=b.person and cast(replace(b.ttime,':00','') as int)>cast(replace(a.ttime,':00','') as int) ) as ttime2 from @t as a where a.ttime<>'12:00 ' and a.ttime<>'17:00'

--select a.iotype,a.person,a.ttime,(select top 1 b.ttime from @t as b where b.person=a.person and iotype=2 order by b.ttime asc) from @t as a where a.iotype=1 order by a.ttime asc
  • 打赏
  • 举报
回复
hyc_music1981 2007-12-18
DECLARE @T TABLE (person varchar(10), ttime varchar(10),Iotype int)
insert into @t
select '001' ,'08:00' ,1 union all --要用08:00,不要用8:00
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'08:00', 1 union all
select '002' ,'12:00',2


select t1.person, t1.ttime in_time, Min(t2.ttime) out_time
from @t t1
left join @t t2 on t1.person=t2.person and t1.ttime<=t2.ttime and t2.Iotype=2
where t1.Iotype=1
group by t1.person,t1.ttime
order by t1.person
  • 打赏
  • 举报
回复
wzy_love_sly 2007-12-18
顶龟
  • 打赏
  • 举报
回复
dawugui 2007-12-18
--sql server 2000中的写法.
create table tb (person varchar(10), ttime varchar(10),Iotype int)
insert into tb
select '001' ,'08:00',1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'08:00',1 union all
select '002' ,'12:00',2

SELECT Wpid=(SELECT COUNT(1) FROM
(
select m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1
) t1 WHERE (person < t2.person) or (person = t2.person and [in] < t2.[in])) + 1 , * from
(
select m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1
) t2
drop table tb

/*
Wpid person in out
-------------------- ---------- ---------- ----------
1 001 08:00 12:00
2 001 13:00 17:00
3 002 08:00 12:00

(3 行受影响)
*/
  • 打赏
  • 举报
回复
dawugui 2007-12-18
--使用sql server 2005的row_number函数可以直接得到前面的序,sql server 2000要麻烦一些.
create table tb (person varchar(10), ttime varchar(10),Iotype int)
insert into tb
select '001' ,'08:00',1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'08:00',1 union all
select '002' ,'12:00',2

select Wpid = row_number() over(order by m.person , m.ttime) , m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1

drop table tb

/*
Wpid person in out
-------------------- ---------- ---------- ----------
1 001 08:00 12:00
2 001 13:00 17:00
3 002 08:00 12:00

(3 行受影响)
*/
  • 打赏
  • 举报
回复
dawugui 2007-12-18
--我将数据略做修改,8:00改为08:00
create table tb (person varchar(10), ttime varchar(10),Iotype int)
insert into tb
select '001' ,'08:00',1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'08:00',1 union all
select '002' ,'12:00',2

select m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1

drop table tb

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

(3 行受影响)
*/
  • 打赏
  • 举报
回复
hui_hui_2007 2007-12-18
先关注一下
  • 打赏
  • 举报
回复
wangxuelid 2007-12-18
DECLARE @T TABLE (person varchar(10), ttime varchar(10),Iotype int)
insert into @t
select '001' , '8:00' ,1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'8:00', 1 union all
select '002' ,'12:00',2
方便大家,,,,
  • 打赏
  • 举报
回复
wangxuelid 2007-12-18
顶,,,
  • 打赏
  • 举报
回复
发帖
MS-SQL Server

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2007-12-18 08:25
社区公告
暂无公告