记录与记录之间的日期比较如何实现呢?

[JOHNCOOLS]

数据库是MS-SQL
表(1)如下
自增序号 字段A 字段B 字段C
1 A 2006-8-1 00:00:10 进
2 A 2006-8-1 01:00:00 进
3 A 2006-8-1 16:00:00 进
4 A 2006-8-3 00:00:50 进
5 A 2006-8-10 00:10:10 进
6 B 2006-8-1 00:00:50 进
7 B 2006-8-2 00:00:50 进
8 B 2006-8-10 00:00:40 进
9 C . .
10 C . .

请问怎样实现对表(1)中的A,B,C中分别进行记录与记录两两之间字段B时间的比较呢?如果之间差大于48小时,就把前一条记录写入表(2)中
例如表(1)中的第4条记录与他下一条记录相比较,刚好超过48小时,就把第4条记录添加到表(2)中


麻烦各位帮我解答一下哦!

[JOHNCOOLS]

这样行吗?因为我是同类才相比啊!就是说就属于A记录才相比!B,C就各自比较属于自己的记录哦!

[jjwang2004]

--楼主试试
------------------------------------------------------------------
declare @t table(a char(10),b datetime,c char(10))
insert into @t
select 'A','2006-8-1 00:00:10','进'
union all
select 'A','2006-8-1 01:00:00','进'
union all
select 'A','2006-8-1 16:00:00','进'
union all
select 'A','2006-8-3 00:00:50','进'
union all
select 'A','2006-8-10 00:10:10','进'
union all
select 'B','2006-8-1 00:00:50','进'
union all
select 'B','2006-8-2 00:00:50','进'
union all
select 'B','2006-8-10 00:00:40', '进'
select id=identity(int,1,1),* into t from @t
select bb.* into tb from t bb,t aa where aa.b>dateadd(hour,48,bb.b)
and aa.id=bb.id+1
select *from tb

drop table t
drop table tb

[hellowork]

如果楼主希望结果是自增序号为4,5,7,8的行,请这样试试:
----创建测试数据
declare @t table(id int,字段A varchar(10),字段B datetime,字段C varchar(10))
insert into @t
select 1,'A','2006-8-1 00:00:10','进' union all
select 2,'A','2006-8-1 01:00:00','进' union all
select 3,'A','2006-8-1 16:00:00','进' union all
select 4,'A','2006-8-3 00:00:50','进' union all
select 5,'A','2006-8-10 00:10:10','进' union all
select 6,'B','2006-8-1 00:00:50','进' union all
select 7,'B','2006-8-2 00:00:50','进' union all
select 8,'B','2006-8-10 00:00:40','进'
----查询
select * from @t a where
datediff(hh,(select top 1 字段B from @t where 字段A = a.字段A and id < a.id order by id desc),a.字段B) >=48
or
datediff(hh,a.字段B,(select top 1 字段B from @t where 字段A = a.字段A and id > a.id order by id asc)) >=48

/*结果
自增序号 字段A 字段B 字段C
4 A 2006-8-3 00:00:50 进
5 A 2006-8-10 00:10:10 进
7 B 2006-8-2 00:00:50 进
8 B 2006-8-10 00:00:40 进
*/

[昵称被占用了]

insert [表(2)]
select *
from [表(1)] a
where not exists (
select 1 from [表(1)]
where [字段A]=a.[字段A] ---猜想需要这个条件
and 字段B>a.字段B
and 字段B<=dateadd(hour,48,a.[字段B]
)

[昵称被占用了]

好想写错了
insert [表(2)]
select *
from [表(1)] a
where not exists (
select 1 from [表(1)]
where [字段A]=a.[字段A] ---猜想需要这个条件
and 字段B<a.字段B
and 字段B>=dateadd(hour,-48,a.[字段B]
)


说明：
第一条记录因为前面没有记录，也会进入，如果需要排除，加条件
and exists (
select 1 from [表(1)]
where [字段A]=a.[字段A] ---猜想需要这个条件
and 字段B<a.字段B
)

[free_pop2k]

declare @t table(a char(10),b datetime,c char(10))
insert into @t
select 'A','2006-8-1 00:00:10','进'
union all
select 'A','2006-8-1 01:00:00','进'
union all
select 'A','2006-8-1 16:00:00','进'
union all
select 'A','2006-8-3 00:00:50','进'
union all
select 'A','2006-8-10 00:10:10','进'
union all
select 'B','2006-8-1 00:00:50','进'
union all
select 'B','2006-8-2 00:00:50','进'
union all
select 'B','2006-8-10 00:00:40', '进'
select id=identity(int,1,1),* into t from @t
select * into tb from t a where exists(select * from t b where a.a=b.a and a.c=b.c and a.id=b.id-1 and datediff(hour,a.b,b.b)>48)
select * from tb
drop table t
drop table tb

[xyxfly]

记录与记录之间的日期比较如何实现呢?

datediff
上面写了很多了,应该可以满足楼主的需求量 ^_^