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

[wangxuelid]

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语句搞顶(不要使用变量或函数,游标之类),,,不胜感激

[wangxuelid]

顶,,,

• 打赏
• 举报

回复 赞

[wangxuelid]

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
方便大家,,,,

• 打赏
• 举报

回复 赞

[hui_hui_2007]

先关注一下

• 打赏
• 举报

回复 赞

[dawugui]

--我将数据略做修改,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 行受影响)
*/

• 打赏
• 举报

回复 赞

[dawugui]

--使用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]

--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 行受影响)
*/

• 打赏
• 举报

回复 赞

[wzy_love_sly]

顶龟

• 打赏
• 举报

回复 赞

[hyc_music1981]

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

• 打赏
• 举报

回复 赞

[conqweal]

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

• 打赏
• 举报

回复 赞

[conqweal]

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]

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

这个是我的最后答案..希望楼主喜欢。

• 打赏
• 举报

回复 赞

[wangxuelid]

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

• 打赏
• 举报

回复 赞

[wangxuelid]

声明: 先感谢大家,来着都有分,,,

• 打赏
• 举报

回复 赞

[tim_spac]

对大家的代码略作调整如下:

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

• 打赏
• 举报

回复 赞

[ljking0731]

--建表
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 行）

• 打赏
• 举报

回复 赞

[ljking0731]

根据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 行）

• 打赏
• 举报

回复 赞