34,591
社区成员
发帖
与我相关
我的任务
分享
--sql2000用临时表
select *,px=(select count(1)+1 from tb where 姓名=t.姓名 and [time]<t.[time])
into #1
from tb t
go
select a.[time],a.姓名,a.biaozhi,px=identity(int,1,1)
into #2
from #1 a,#1 b
where a.姓名=b.姓名 and a.px=b.px+1
and (a.biaozhi=1 and b.biaozhi=2 or a.biaozhi=2 and b.biaozhi=1)
go
select
开始时间= max(case when biaozhi=2 then [time] else '' end),
结束时间= max(case when biaozhi=1 then [time] else '' end),
姓名
from #2
group by 姓名,(px-1)/2
/**
开始时间 结束时间 姓名
----------------------- ----------------------- ----
2010-07-09 11:13:13.000 2010-07-11 11:13:13.000 s
(1 行受影响)
**/
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Time] datetime,[biaozhi] int,[姓名] varchar(1))
insert [tb]
select '2010-07-08 12:34:56',1,'s' union all
select '2010-07-08 16:17:56',1,'s' union all
select '2010-07-09 11:13:13',2,'s' union all
select '2010-07-09 19:13:13',2,'s' union all
select '2010-07-10 11:13:13',2,'s' union all
select '2010-07-11 11:13:13',1,'s' union all
select '2010-07-12 14:13:13',1,'s'
go
---查询---
if object_id('p_test') is not null drop proc p_test
go
create proc p_test
as
begin
create table #t (id int identity,开始时间 datetime,结束时间 datetime,姓名 varchar(1) )
declare @time datetime,@biaozhi1 int,@biaozhi int,@姓名 varchar(1),@i int,@id int
declare cur cursor for select * from tb
open cur
fetch cur into @time,@biaozhi,@姓名
set @i=0
while @@fetch_status=0
begin
if @biaozhi1=1 and @biaozhi=2
begin
insert #t (开始时间,姓名) select @time,@姓名
select @id=scope_identity()
set @i=@i+1
end
if @biaozhi1 = 2 and @biaozhi=1
begin
update #t set 结束时间 = @time,姓名=@姓名 where id=@id
set @i=@i+1
end
if @i=2
set @i=0
set @biaozhi1=@biaozhi
fetch cur into @time,@biaozhi,@姓名
end
close cur
deallocate cur
select * from #t
end
go
p_test
/*
期望的查询结果为:
id 开始时间 结束时间 姓名
----------- ----------------------- ----------------------- ----
1 2010-07-09 11:13:13.000 2010-07-11 11:13:13.000 s
(1 行受影响)
*/
---查询---
select
开始时间= max(case when biaozhi=2 then [time] else '' end),
结束时间= max(case when biaozhi=1 then [time] else '' end),
姓名
from(
select a.[time],a.姓名,a.biaozhi,rn=row_number() over(order by a.[time])
from
(select *,rn=row_number() over(partition by 姓名 order by [time])
from tb
) a,
(select *,rn=row_number() over(partition by 姓名 order by [time])
from tb
) b
where a.姓名=b.姓名 and a.rn=b.rn+1
and (a.biaozhi=1 and b.biaozhi=2 or a.biaozhi=2 and b.biaozhi=1)
) t
group by 姓名,(rn-1)/2
---结果---
开始时间 结束时间 姓名
----------------------- ----------------------- ----
2010-07-09 11:13:13.000 2010-07-11 11:13:13.000 s
(1 行受影响)
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Time] datetime,[biaozhi] int,[姓名] varchar(1))
insert [tb]
select '2010-07-08 12:34:56',1,'s' union all
select '2010-07-08 16:17:56',1,'s' union all
select '2010-07-09 11:13:13',2,'s' union all
select '2010-07-09 19:13:13',2,'s' union all
select '2010-07-10 11:13:13',2,'s' union all
select '2010-07-11 11:13:13',1,'s' union all
select '2010-07-12 14:13:13',1,'s'
---查询---
select
开始时间= max(case when a.biaozhi=2 then a.[time] else '' end),
结束时间= max(case when a.biaozhi=1 then a.[time] else '' end),
a.姓名
from
(select *,rn=row_number() over(partition by 姓名 order by [time])
from tb
) a,
(select *,rn=row_number() over(partition by 姓名 order by [time])
from tb
) b
where a.姓名=b.姓名 and a.rn=b.rn+1
and (a.biaozhi=1 and b.biaozhi=2 or a.biaozhi=2 and b.biaozhi=1)
group by a.姓名
---结果---
开始时间 结束时间 姓名
----------------------- ----------------------- ----
2010-07-09 11:13:13.000 2010-07-11 11:13:13.000 s
(1 行受影响)
create table #tb(Time datetime, biaozhi int ,姓名 varchar(20))
go
insert into #tb
select '2010-07-08 12:34:56','1','s'
union all select '2010-07-08 16:17:56','1','s'
union all select '2010-07-09 11:13:13','2','s'
union all select '2010-07-09 19:13:13','2','s'
union all select '2010-07-10 11:13:13','2','s'
union all select '2010-07-11 11:13:13','1','s'
union all select '2010-07-12 14:13:13','1','s'
go
select top 1
Time as 开始时间,
(select top 1 b.time from #tb b where a.姓名 = b.姓名 and b.biaozhi = 1 and b.Time > a.Time) as 结束时间,
姓名
from #tb a
where biaozhi = 2
/*
开始时间 结束时间 姓名
----------------------- ----------------------- --------------------
2010-07-09 11:13:13.000 2010-07-11 11:13:13.000 s
(1 行受影响)
*/