如何对表中前后两条记录的同一属性值进行计算？

Question

如何对表中前后两条记录进行计算？ 
怎么计算前后两条记录时间字段的差值呢？比如：
uer sessionid pathid timeid
1         1       2   0:0:0
1         1       1   0:0:15
1         1       3   0:0:40
1         2       2   0:3:15
1         2       4   0:3:40
2         3       2   0:4:0
2         3       1   0:4:15
2         3       3   0:4:40
2         4       2   0:5:15
2         4       4   0:5:40
希望得到以下表，添加的timestay属性为pathid的持续时间，其中每个session的最后一个pathid所对应的timestay值设置为0，即下表
uer sessionid pathid    timestay（秒）
1        1       2        15
1        1       1        25
1        1       3        0
1        2       2        25 
1        2       4        0
2        3       2        15
2        3       1        25
2        3       3        0
2        4       2        25
2        4       4        0
还请各位再帮帮忙啊！

Answer 1

select uer, sessionid ,pathid ,
datediff(s, timeid,isnull(b.timeid,a.timeid))  as timestayfrom table1 a left outer join table1 b on a.sessionid=b.sessionid and a.timeid <b.timeid 

Answer 2

create table #q (uer int, sessionid int ,pathid int ,timeid varchar(20))
insert into #q
select 1 ,        1 ,      2,   '0:0:0' union all
select 1,         1 ,      1,   '0:0:15' union all
select 1,         1 ,      3,   '0:0:40' union all
select 1,         2 ,      2,   '0:3:15' union all
select 1,         2 ,      4,   '0:3:40' union all
select 2,         3 ,      2,   '0:4:0' union all
select 2,         3 ,      1,   '0:4:15' union all
select 2,         3 ,      3,   '0:4:40' union all
select 2,         4 ,      2,   '0:5:15' union all
select 2,         4 ,      4,   '0:5:40'
select a.* ,isnull(datediff(ss,a.timeid,min(b.timeid)),0)    from #q a left join #q b 
on a.uer=b.uer and  a.sessionid =b.sessionid and a.timeid<b.timeid
group by a.uer, a.sessionid ,a.pathid ,a.timeid
order by a.timeid/**
uer         sessionid   pathid      timeid                           
----------- ----------- ----------- -------------------- ----------- 
1           1           2           0:0:0                15
1           1           1           0:0:15               25
1           1           3           0:0:40               0
1           2           2           0:3:15               25
1           2           4           0:3:40               0
2           3           2           0:4:0                15
2           3           1           0:4:15               25
2           3           3           0:4:40               0
2           4           2           0:5:15               25
2           4           4           0:5:40               0（所影响的行数为 10 行）**/

Answer 3

try:CREATE TABLE #T(uer int,sessionid int, pathid int,timeid datetime)
INSERT INTO #T
SELECT 1         ,1       ,2   ,'0:0:0' UNION ALL
SELECT 1         ,1       ,1   ,'0:0:15' UNION ALL
SELECT 1         ,1       ,3   ,'0:0:40' UNION ALL
SELECT 1         ,2       ,2   ,'0:3:15' UNION ALL
SELECT 1         ,2       ,4   ,'0:3:40' UNION ALL
SELECT 2         ,3       ,2   ,'0:4:0' UNION ALL
SELECT 2         ,3       ,1   ,'0:4:15' UNION ALL
SELECT 2         ,3       ,3   ,'0:4:40' UNION ALL
SELECT 2         ,4       ,2   ,'0:5:15' UNION ALL
SELECT 2         ,4       ,4   ,'0:5:40' 
SELECT A.uer,A.sessionid,A.pathid,ISNULL(DATEDIFF(second,A.timeid,B.timeid),0) AS timestay FROM  #T AS A 
LEFT OUTER JOIN #T AS B ON B.sessionid =A.sessionid AND B.timeid>A.timeid 
AND NOT EXISTS(SELECT 1 FROM #T AS B1 WHERE B1.sessionid=B.sessionid AND B1.timeid>A.timeid  AND B1.timeid<B.timeid  )
DROP TABLE #T
/*
uer sessionid pathid    timestay
1        1       2        15
1        1       1        25
1        1       3        0
1        2       2        25 
1        2       4        0
2        3       2        15
2        3       1        25
2        3       3        0
2        4       2        25
2        4       4        0
*/

Answer 4

create table test0702(uer int,sessionid int,pathid int, timeid datetime)
insert test0702 select 1,         1,       2 ,  '0:0:0' union all select
1,         1,       1,   '0:0:15' union all select
1 ,        1 ,      3 ,  '0:0:40' union all select
1  ,       2  ,     2  , '0:3:15' union all select
1   ,      2   ,    4   ,'0:3:40' union all select
2    ,     3    ,   2   ,'0:4:0' union all select
2     ,    3     ,  1   ,'0:4:15' union all select
2      ,   3      , 3   ,'0:4:40' union all select
2       ,  4       ,2   ,'0:5:15' union all select
2        , 4       ,4   ,'0:5:40'select * from test0702
truncate table test0702select *,identity(int) as id into # from test0702
insert #(uer,sessionid,pathid,timeid) select top 1 uer,sessionid,pathid,timeid from # order by id descselect a.uer,a.sessionid,a.pathid,a.timeid,datediff(second,a.timeid,b.timeid) as timestay from # a join # b on a.id=b.id-1

Answer 5

create table test0702(uer int,sessionid int,pathid int, timeid datetime)
insert test0702 select 1,         1,       2 ,  '0:0:0' union all select
1,         1,       1,   '0:0:15' union all select
1 ,        1 ,      3 ,  '0:0:40' union all select
1  ,       2  ,     2  , '0:3:15' union all select
1   ,      2   ,    4   ,'0:3:40' union all select
2    ,     3    ,   2   ,'0:4:0' union all select
2     ,    3     ,  1   ,'0:4:15' union all select
2      ,   3      , 3   ,'0:4:40' union all select
2       ,  4       ,2   ,'0:5:15' union all select
2        , 4       ,4   ,'0:5:40'
select *,identity(int) as id into # from test0702
insert #(uer,sessionid,pathid,timeid) select top 1 uer,sessionid,pathid,timeid from # order by id descselect a.uer,a.sessionid,a.pathid,a.timeid,datediff(second,a.timeid,b.timeid) as timestay from # a join # b on a.id=b.id-1

Answer 6

to ljsql：
谢谢，你的程序我运行了以下，如果同一session中pathID或timeID不同就没问题，不然就完全不符合要求了。比如
         user   sessionid  pathid        timeid
1 1 2 2004-12-13 0:00:45 1
1 1 1 2004-12-13 0:00:45 1
1 1 1 2004-12-13 0:00:45 2
1 1 3 2004-12-13 0:00:46 2
1 128 12 2004-12-13 0:43:26 1
1 128 8 2004-12-13 0:43:26 1
2 2 4 2004-12-13 0:02:26 1
2 2 2 2004-12-13 0:02:29 1
2 2 5 2004-12-13 0:02:45 1
2 119 4 2004-12-13 0:40:23 1
2 119 2 2004-12-13 0:40:27 1
运行后会得到
1 1 1 2004-12-13 00:00:45.000 1
1 1 2 2004-12-13 00:00:45.000 1
1 1 3 2004-12-13 00:00:46.000 0
2 2 4 2004-12-13 00:02:26.000 3
2 2 2 2004-12-13 00:02:29.000 16
2 2 5 2004-12-13 00:02:45.000 0而我期望的是1 2 2004-12-13 00:00:45.000 0
1   1 1 2004-12-13 00:00:45.000 0
1   1 1 2004-12-13 00:00:45.000 1
1   1 3 2004-12-13 00:00:46.000 0
1   128 12 2004-12-13 00:43:26.000 0
1   128 8 2004-12-13 00:43:26.000 0
2   2 4 2004-12-13 00:02:26.000 3
2   2 2 2004-12-13 00:02:29.000 16
2   2 5 2004-12-13 00:02:45.000 0
2   119 4 2004-12-13 00:40:23.000     4
2   119 2 2004-12-13 00:40:27.000 0

Answer 7

to: RunUpwind
 运行之后结果最符合要求，但是在 sessionid有变化的时候timestay数值不对
uer sessionid pathid timeid
1         1       2   0:0:0
1         1       1   0:0:15
1         1       3   0:0:40
1         2       2   0:3:15
希望在倒数第二条记录得到的timestay是0秒而不是（0:3:15-0:0:40）155
不知道还能否做出改进，谢谢！

Answer 8

我转发一个网友的回复，虽然不是原创，还是可以拿来和大家共享的，方法复杂了点，但是答案是完全正确的--创建测试表a
create table a(uer int,sessionid int,pathid int,timeid varchar(20))
insert a values(1,1,2,'0:0:45')
insert a values(1,1,1,'0:0:45')
insert a values(1,1,1,'0:0:45')
insert a values(1,1,3,'0:0:46')
insert a values(1,128,12,'0:43:26')
insert a values(1,128,8,'0:43:26')
insert a values(8,8,4,'0:9:8')
insert a values(8,8,3,'0:9:9')
insert a values(8,8,2,'0:9:9')
insert a values(8,8,7,'0:10:20')go
--创建临时表#tmp1并插入数据
create table #tmp1(num int identity(1,1),uer int, sessionid int, pathid int, timeid datetime)
insert #tmp1 select * from a
--定义游标
declare @num int
declare @uer int
declare @sessionid int
declare @pathid int
declare @timeid datetime
declare @tmp_timeid datetime
declare @tmp_uer int
declare @tmp_sessionid int
create table #tmp(num int,uer int,sessionid int,pathid int,timeid int)declare test_cursor cursor for select num,uer,sessionid,pathid,timeid from #tmp1 order by num desc
open test_cursorfetch next from test_cursor into @num,@uer,@sessionid,@pathid,@timeid
while @@fetch_status = 0
begin
if (select count(*) from #tmp)=0
begin
insert #tmp values(@num,@uer,@sessionid,@pathid,0)
end
else
begin
select @tmp_uer=uer,@tmp_sessionid=sessionid
from #tmp 
where num=(select min(num) from #tmp)
select @tmp_timeid=timeid from #tmp1 where num=(select min(num) from #tmp)
if (@uer=@tmp_uer) and (@sessionid=@tmp_sessionid)
begin
insert #tmp values(@num,@uer,@sessionid,@pathid,datediff(ss,@timeid,@tmp_timeid))
end
else
begin
insert #tmp values(@num,@uer,@sessionid,@pathid,0)
end
end
fetch next from test_cursor into @num,@uer,@sessionid,@pathid,@timeid
endclose test_cursor
deallocate test_cursor
select uer,sessionid,pathid,timeid as timestay from #tmp order by num
go

Answer 9

我想问你一下~你的主键是那个~一开始我还以为你的user ,  sessionid成一全主键,你这个只要加上一个主键就可以了~ 不要就加上一个identity字段,这样就不用游标,用游标感觉实不爽