34,837
社区成员
发帖
与我相关
我的任务
分享思考一天毫无结果!!!!!求高人赐教
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语句搞顶(不要使用变量或函数,游标之类),,,不胜感激
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语句搞顶(不要使用变量或函数,游标之类),,,不胜感激
...全文
请发表友善的回复…
发表回复
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
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
这个是我的最后答案..希望楼主喜欢。
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'
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
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
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
方便大家,,,,
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
- 打赏
- 举报
顶,,,
