--测试数据
create table #T (Emp varchar(5),IntTime datetime,IntType varchar(5))
insert into #T
select '1','2005-08-11 14:39:33.000','0' union all
select '1','2005-08-11 14:39:40.000','1' union all
select '1','2005-08-11 14:56:54.000','0' union all
select '1','2005-08-13 12:47:59.000','0' union all
select '1','2005-08-13 22:54:26.000','1' union all
select '2','2005-08-13 10:56:05.000','0' union all
select '2','2005-08-13 22:18:14.000','1' union all
select '8','2005-08-11 14:58:33.000','0' union all
select '8','2005-08-11 15:01:02.000','1' union all
select '8','2005-08-11 15:02:48.000','0' /*
Emp IntTime IntType
----- ------------------------------------------------------ -------
1 2005-08-11 14:39:33.000 0
1 2005-08-11 14:39:40.000 1
1 2005-08-11 14:56:54.000 0
1 2005-08-13 12:47:59.000 0
1 2005-08-13 22:54:26.000 1
2 2005-08-13 10:56:05.000 0
2 2005-08-13 22:18:14.000 1
8 2005-08-11 14:58:33.000 0
8 2005-08-11 15:01:02.000 1
8 2005-08-11 15:02:48.000 0(10 row(s) affected)
*/select identity(int,1,1) as sn ,* into #S from #T order by emp,inttime
--依照emp,inttime排序,塞入临时表,并且加个序号snselect * from #s
/*
sn Emp IntTime IntType
----------- ----- ------------------------------------------------------ -------
1 1 2005-08-11 14:39:33.000 0
2 1 2005-08-11 14:39:40.000 1
3 1 2005-08-11 14:56:54.000 0
4 1 2005-08-13 12:47:59.000 0
5 1 2005-08-13 22:54:26.000 1
6 2 2005-08-13 10:56:05.000 0
7 2 2005-08-13 22:18:14.000 1
8 8 2005-08-11 14:58:33.000 0
9 8 2005-08-11 15:01:02.000 1
10 8 2005-08-11 15:02:48.000 0(10 row(s) affected
*/
select emp,(case when s1.IntType = '0' then select IntTime from #S S2 where S2.sn = select * from #S S11 2005-08-11 14:39:33.000 2005-08-11 14:39:40.000
1 2005-08-11 14:56:54.000
1 2005-08-13 12:47:59.000 2005-08-13 22:54:26.000
--查询语句
select Emp,IntTime=convert(varchar(20),IntTime,120),OutTime=
isnull((select convert(varchar(20),inttime,120) from #s s2 where (s2.sn - 1) = s1.sn
and s2.inttype = '1' and s2.emp = s1.emp),'')
from #S s1
where s1.inttype = '0'
order by s1.emp,s1.inttime
/*
Emp IntTime OutTime
----- -------------------- --------------------
1 2005-08-11 14:39:33 2005-08-11 14:39:40
1 2005-08-11 14:56:54
1 2005-08-13 12:47:59 2005-08-13 22:54:26
2 2005-08-13 10:56:05 2005-08-13 22:18:14
8 2005-08-11 14:58:33 2005-08-11 15:01:02
8 2005-08-11 15:02:48 (6 row(s) affected)
*/
create table #T (Emp varchar(5),IntTime datetime,IntType varchar(5))
insert into #T
select '1','2005-08-11 14:39:33.000','0' union all
select '1','2005-08-11 14:39:40.000','1' union all
select '1','2005-08-11 14:56:54.000','0' union all
select '1','2005-08-13 12:47:59.000','0' union all
select '1','2005-08-13 22:54:26.000','1' union all
select '2','2005-08-13 10:56:05.000','0' union all
select '2','2005-08-13 22:18:14.000','1' union all
select '8','2005-08-11 14:58:33.000','0' union all
select '8','2005-08-11 15:01:02.000','1' union all
select '8','2005-08-11 15:02:48.000','0' /*
Emp IntTime IntType
----- ------------------------------------------------------ -------
1 2005-08-11 14:39:33.000 0
1 2005-08-11 14:39:40.000 1
1 2005-08-11 14:56:54.000 0
1 2005-08-13 12:47:59.000 0
1 2005-08-13 22:54:26.000 1
2 2005-08-13 10:56:05.000 0
2 2005-08-13 22:18:14.000 1
8 2005-08-11 14:58:33.000 0
8 2005-08-11 15:01:02.000 1
8 2005-08-11 15:02:48.000 0(10 row(s) affected)
*/select identity(int,1,1) as sn ,* into #S from #T order by emp,inttime
--依照emp,inttime排序,塞入临时表,并且加个序号snselect * from #s
/*
sn Emp IntTime IntType
----------- ----- ------------------------------------------------------ -------
1 1 2005-08-11 14:39:33.000 0
2 1 2005-08-11 14:39:40.000 1
3 1 2005-08-11 14:56:54.000 0
4 1 2005-08-13 12:47:59.000 0
5 1 2005-08-13 22:54:26.000 1
6 2 2005-08-13 10:56:05.000 0
7 2 2005-08-13 22:18:14.000 1
8 8 2005-08-11 14:58:33.000 0
9 8 2005-08-11 15:01:02.000 1
10 8 2005-08-11 15:02:48.000 0(10 row(s) affected
*/
select emp,(case when s1.IntType = '0' then select IntTime from #S S2 where S2.sn = select * from #S S11 2005-08-11 14:39:33.000 2005-08-11 14:39:40.000
1 2005-08-11 14:56:54.000
1 2005-08-13 12:47:59.000 2005-08-13 22:54:26.000
--查询语句
select Emp,IntTime=convert(varchar(20),IntTime,120),OutTime=
isnull((select convert(varchar(20),inttime,120) from #s s2 where (s2.sn - 1) = s1.sn
and s2.inttype = '1' and s2.emp = s1.emp),'')
from #S s1
where s1.inttype = '0'
order by s1.emp,s1.inttime
/*
Emp IntTime OutTime
----- -------------------- --------------------
1 2005-08-11 14:39:33 2005-08-11 14:39:40
1 2005-08-11 14:56:54
1 2005-08-13 12:47:59 2005-08-13 22:54:26
2 2005-08-13 10:56:05 2005-08-13 22:18:14
8 2005-08-11 14:58:33 2005-08-11 15:01:02
8 2005-08-11 15:02:48 (6 row(s) affected)
*/
go
create table t1(emp int,inttime varchar(50),inttype int)
go
insert into t1
select '1','2005-08-11 14:39:33.000','0' union all
select '1','2005-08-11 14:39:40.000','1' union all
select '1','2005-08-11 14:56:54.000','0' union all
select '1','2005-08-13 12:47:59.000','0' union all
select '1','2005-08-13 22:54:26.000','1' union all
select '2','2005-08-13 10:56:05.000','0' union all
select '2','2005-08-13 22:18:14.000','1' union all
select '8','2005-08-11 14:58:33.000','0' union all
select '8','2005-08-11 15:01:02.000','1' union all
select '8','2005-08-11 15:02:48.000','0'
go
--
declare @emp int,@inttime varchar(50),@type int,@flag varchar(2)
declare @emp2 int,@id intdeclare cur cursor for
select emp,inttime,inttype from t1
open curfetch next from cur into @emp,@inttime,@type
while @@fetch_status=0
begin
set @flag=(select top 1 flag from t2 order by [id] desc)
set @emp2=(select top 1 emp from t2 order by [id] desc)
set @id=(select max(id) from t2 ) if len(@flag)=1 and @flag<>cast(@type as varchar(2)) and @emp=@emp2
begin
if @type=0
update t2 set inttime=@inttime,flag='10' where id=@id
else
update t2 set outtime=@inttime,flag='01' where id=@id
end
else
begin
if @type=0
insert into t2(emp,inttime,outtime,flag) select @emp,@inttime,'','0'
else
insert into t2(emp,inttime,outtime,flag) select @emp,'',@inttime,'1'
end
fetch next from cur into @emp,@inttime,@type
end
close cur
deallocate cur--测试
select * from t1
select * from t2
--结果
/*
emp inttime inttype
----------- -------------------------------------------------- -----------
1 2005-08-11 14:39:33.000 0
1 2005-08-11 14:39:40.000 1
1 2005-08-11 14:56:54.000 0
1 2005-08-13 12:47:59.000 0
1 2005-08-13 22:54:26.000 1
2 2005-08-13 10:56:05.000 0
2 2005-08-13 22:18:14.000 1
8 2005-08-11 14:58:33.000 0
8 2005-08-11 15:01:02.000 1
8 2005-08-11 15:02:48.000 0(所影响的行数为 10 行)id emp inttime outtime flag
----------- ----------- -------------------------------------------------- -------------------------------------------------- ----
1 1 2005-08-11 14:39:33.000 2005-08-11 14:39:40.000 01
2 1 2005-08-11 14:56:54.000 0
3 1 2005-08-13 12:47:59.000 2005-08-13 22:54:26.000 01
4 2 2005-08-13 10:56:05.000 2005-08-13 22:18:14.000 01
5 8 2005-08-11 14:58:33.000 2005-08-11 15:01:02.000 01
6 8 2005-08-11 15:02:48.000 0 (所影响的行数为 6 行)*/
go
create table t1(emp int,inttime varchar(50),inttype int)
go
insert into t1
select '1','2005-08-11 14:39:33.000','0' union all
select '1','2005-08-11 14:39:40.000','1' union all
select '1','2005-08-11 14:56:54.000','0' union all
select '1','2005-08-13 12:47:59.000','0' union all
select '1','2005-08-13 22:54:26.000','1' union all
select '2','2005-08-13 10:56:05.000','0' union all
select '2','2005-08-13 22:18:14.000','1' union all
select '8','2005-08-11 14:58:33.000','0' union all
select '8','2005-08-11 15:01:02.000','1' union all
select '8','2005-08-11 15:02:48.000','0'
go
--
declare @emp int,@inttime varchar(50),@type int,@flag varchar(2)
declare @emp2 int,@id intdeclare cur cursor for
select emp,inttime,inttype from t1
open curfetch next from cur into @emp,@inttime,@type
while @@fetch_status=0
begin
set @flag=(select top 1 flag from t2 order by [id] desc)
set @emp2=(select top 1 emp from t2 order by [id] desc)
set @id=(select max(id) from t2 ) if len(@flag)=1 and @flag<>cast(@type as varchar(2)) and @emp=@emp2
begin
if @type=0
update t2 set inttime=@inttime,flag='10' where id=@id
else
update t2 set outtime=@inttime,flag='01' where id=@id
end
else
begin
if @type=0
insert into t2(emp,inttime,outtime,flag) select @emp,@inttime,'','0'
else
insert into t2(emp,inttime,outtime,flag) select @emp,'',@inttime,'1'
end
fetch next from cur into @emp,@inttime,@type
end
close cur
deallocate cur--测试
select * from t1
select * from t2
--结果
/*
emp inttime inttype
----------- -------------------------------------------------- -----------
1 2005-08-11 14:39:33.000 0
1 2005-08-11 14:39:40.000 1
1 2005-08-11 14:56:54.000 0
1 2005-08-13 12:47:59.000 0
1 2005-08-13 22:54:26.000 1
2 2005-08-13 10:56:05.000 0
2 2005-08-13 22:18:14.000 1
8 2005-08-11 14:58:33.000 0
8 2005-08-11 15:01:02.000 1
8 2005-08-11 15:02:48.000 0(所影响的行数为 10 行)id emp inttime outtime flag
1 1 2005-08-11 14:39:33.000 2005-08-11 14:39:40.000 012 1 2005-08-11 14:56:54.000 0 3 1 2005-08-13 12:47:59.000 2005-08-13 22:54:26.000 014 2 2005-08-13 10:56:05.000 2005-08-13 22:18:14.000 015 8 2005-08-11 14:58:33.000 2005-08-11 15:01:02.000 016 8 2005-08-11 15:02:48.000 0 (所影响的行数为 6 行)*/
create table #T (Emp varchar(5),IntTime datetime,IntType varchar(5))
insert into #T
select '1','2005-08-11 14:39:33.000','0' union all
select '1','2005-08-11 14:39:40.000','1' union all
select '1','2005-08-11 14:56:54.000','0' union all
select '1','2005-08-11 14:56:55.000','0' union all -- 测试添加的列
select '1','2005-08-13 12:47:59.000','0' union all
select '1','2005-08-13 22:54:26.000','1' union all
select '2','2005-08-13 10:56:05.000','0' union all
select '2','2005-08-13 22:18:14.000','1' union all
select '8','2005-08-11 14:58:33.000','0' union all
select '8','2005-08-11 15:01:02.000','1' union all
select '8','2005-08-11 15:02:48.000','0' union all
select '9','2005-08-11 14:58:33.000','1' union all -- 测试添加的列
select '9','2005-08-11 15:01:02.000','1' union all
select '9','2005-08-11 15:01:05.000','1' union all
select '9','2005-08-11 15:02:48.000','0' union all
select '9','2005-08-11 15:02:49.000','1'
goselect emp,-- id=case when id0>id1 then id0 else id1 end,
IntTime=max(case IntType when 0 then IntTime else '' end),
OutTime=max(case IntType when 1 then IntTime else '' end)
from(
select Emp,IntTime=stuff(convert(varchar(30),IntTime,113),1,10,convert(char(10),IntTime,120)),IntType,
id0=(select count(*) from #T where Emp=a.Emp and IntType=0 and IntTime<=a.IntTime),
id1=(select count(*) from #T where Emp=a.Emp and IntType=1 and IntTime<a.IntTime)
from #T a
)a group by emp,case when id0>id1 then id0 else id1 end
order by emp,case when id0>id1 then id0 else id1 end
godrop table #t/*--结果emp IntTime OutTime
----- ------------------------------ ------------------------------
1 2005-08-11 14:39:33:000 2005-08-11 14:39:40:000
1 2005-08-11 14:56:54:000
1 2005-08-11 14:56:55:000
1 2005-08-13 12:47:59:000 2005-08-13 22:54:26:000
2 2005-08-13 10:56:05:000 2005-08-13 22:18:14:000
8 2005-08-11 14:58:33:000 2005-08-11 15:01:02:000
8 2005-08-11 15:02:48:000
9 2005-08-11 14:58:33:000
9 2005-08-11 15:01:02:000
9 2005-08-11 15:01:05:000
9 2005-08-11 15:02:48:000 2005-08-11 15:02:49:000(所影响的行数为 11 行)
--*/