数据库表A存有如下类型的字段:
Time biaozhi 姓名
2010-07-08 12:34:56 1 s
2010-07-08 16:17:56 1 s
2010-07-09 11:13:13 2 s
2010-07-09 19:13:13 2 s
2010-07-10 11:13:13 2 s
2010-07-11 11:13:13 1 s
2010-07-12 14:13:13 1 s
biaozhi为一个标志字段。当标志从1变到2时,该Time字段的值为开始时间,当标志从2变到1时,Time字段的值为结束时间。
期望的查询结果为:
开始时间 结束时间 姓名
2010-07-09 11:13:13 2010-07-11 11:13:13 s请问大侠,这该如何实现?是在SQL语句里实现呢,还是对查询的结果进行过滤判断呀?就是希望把结果显示到stringgrid上。SQL语句可以实现么?
各位大侠!指教!!!
Time biaozhi 姓名
2010-07-08 12:34:56 1 s
2010-07-08 16:17:56 1 s
2010-07-09 11:13:13 2 s
2010-07-09 19:13:13 2 s
2010-07-10 11:13:13 2 s
2010-07-11 11:13:13 1 s
2010-07-12 14:13:13 1 s
biaozhi为一个标志字段。当标志从1变到2时,该Time字段的值为开始时间,当标志从2变到1时,Time字段的值为结束时间。
期望的查询结果为:
开始时间 结束时间 姓名
2010-07-09 11:13:13 2010-07-11 11:13:13 s请问大侠,这该如何实现?是在SQL语句里实现呢,还是对查询的结果进行过滤判断呀?就是希望把结果显示到stringgrid上。SQL语句可以实现么?
各位大侠!指教!!!
--> 测试数据: @tb
declare @tb table ([Time] datetime,biaozhi int,姓名 varchar(1))
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'
select distinct
mindt 开始时间,
(select top 1 [Time]
from @tb
where 姓名=a.姓名 and biaozhi=1 and [Time]>=a.maxdt order by [Time])结束时间,
a.姓名
from
(
select min([Time])mindt,
max([Time])maxdt,
姓名
from @tb
where biaozhi=2
group by 姓名)a,@tb b
where a.姓名=b.姓名
/*
开始时间 结束时间 姓名
----------------------- ----------------------- ----
2010-07-09 11:13:13.000 2010-07-11 11:13:13.000 s(1 行受影响)
*/
select case when biaozhi='1' 开始时间
when biaozhi='2' 结束时间
end as 时间
from 表
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'
goselect 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 行受影响)*/
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 行受影响)
---查询---
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'
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 *,px=(select count(1)+1 from tb where 姓名=t.姓名 and [time]<t.[time])
into #1
from tb t
goselect 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)
goselect
开始时间= 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 行受影响)**/
谢谢你们的帮助。问题终于解决了。用的是josy大哥23#的方法。
结贴。
http://topic.csdn.net/u/20100713/08/1748105a-ad62-4542-9411-069c26c3a7e7.html