一个表的记录之间的日期间隔怎么取，太难了,请求SQL高手支援！！！
有以下表结构(tablename)
ID UserID thedate thetime(单位：分钟)
-------------------------------
1AL0009841899-12-30 10:41:00
5AL0009841899-12-30 10:42:00
6AL0009841899-12-30 10:46:00
14AL0009841899-12-30 10:57:00
17BL0009851899-12-30 11:06:00
18BL0009851899-12-30 11:14:00
44BL0009851899-12-30 11:24:00
50BL0009851899-12-30 11:36:00
51BL0009851899-12-30 11:46:00
tablename中有多个用户（现在示例中仅列出两个用户AL000984和BL000985），thedate字段是一个日期时间字段，是某业务操作时间，有先后顺序，现在我想
根据用户来取得相邻两记录相差的时间值，并写入thetime字段内,单位为分钟,
即要得下面的结果：
ID UserID thedate thetime(单位：分钟)
-------------------------------
1AL0009841899-12-30 10:41:000
5AL0009841899-12-30 10:42:001
6AL0009841899-12-30 10:46:004
14AL0009841899-12-30 10:57:0011
17BL0009851899-12-30 11:06:000
18BL0009851899-12-30 11:14:008
44BL0009851899-12-30 11:24:0010
50BL0009851899-12-30 11:36:0012
51BL0009851899-12-30 11:46:0010
希望用一条SQL语句解决，谢谢大侠门！本人对在一个表中进行不同记录之间的比较很少做，不知从何下手
[解决办法]
- SQL code
create table tb(ID int,UserID varchar(10),thedate varchar(10),thetime varchar(10))insert into tb values(1 ,'AL000984', '1899-12-30', '10:41:00') insert into tb values(5 ,'AL000984', '1899-12-30', '10:42:00') insert into tb values(6 ,'AL000984', '1899-12-30', '10:46:00') insert into tb values(14 ,'AL000984', '1899-12-30', '10:57:00') insert into tb values(17 ,'BL000985', '1899-12-30', '11:06:00') insert into tb values(18 ,'BL000985', '1899-12-30', '11:14:00') insert into tb values(44 ,'BL000985', '1899-12-30', '11:24:00') insert into tb values(50 ,'BL000985', '1899-12-30', '11:36:00') insert into tb values(51 ,'BL000985', '1899-12-30', '11:46:00') goselect t.id,t.UserID,t.thedate,t.thetime,[minute] = 0 from( select px=(select count(1) from tb where UserID=a.UserID and cast(thedate + ' ' + thetime as datetime)<cast(a.thedate + ' ' + a.thetime as datetime))+1 , * from tb a) t where px = 1union allselect n.id,n.UserID,n.thedate,n.thetime,[minute]=datediff(minute,cast(m.thedate + ' ' + m.thetime as datetime),cast(n.thedate + ' ' + n.thetime as datetime)) from( select px=(select count(1) from tb where UserID=a.UserID and cast(thedate + ' ' + thetime as datetime)<cast(a.thedate + ' ' + a.thetime as datetime))+1 , * from tb a) m,( select px=(select count(1) from tb where UserID=a.UserID and cast(thedate + ' ' + thetime as datetime)<cast(a.thedate + ' ' + a.thetime as datetime))+1 , * from tb a) nwhere m.UserID = n.UserID and m.px = n.px - 1 order by t.userid,t.thedate,t.thetimedrop table tb/*id UserID thedate thetime minute ----------- ---------- ---------- ---------- ----------- 1 AL000984 1899-12-30 10:41:00 05 AL000984 1899-12-30 10:42:00 16 AL000984 1899-12-30 10:46:00 414 AL000984 1899-12-30 10:57:00 1117 BL000985 1899-12-30 11:06:00 018 BL000985 1899-12-30 11:14:00 844 BL000985 1899-12-30 11:24:00 1050 BL000985 1899-12-30 11:36:00 1251 BL000985 1899-12-30 11:46:00 10*/
[解决办法]
- SQL code
create table tb(ID int,UserID varchar(10),thedate datetime,thetimes int)insert into tb(id,UserID,thedate) values(1 ,'AL000984', '1899-12-30 10:41:00') insert into tb(id,UserID,thedate) values(5 ,'AL000984', '1899-12-30 10:42:00') insert into tb(id,UserID,thedate) values(6 ,'AL000984', '1899-12-30 10:46:00')(
[解决办法]
- SQL code
declare @t table (ID int,UserID varchar(10),thedate varchar(10),thetime varchar(10))
insert into @t values(1 ,'AL000984', '1899-12-30', '10:41:00')
insert into @t values(5 ,'AL000984', '1899-12-30', '10:42:00')
insert into @t values(6 ,'AL000984', '1899-12-30', '10:46:00')
insert into @t values(14 ,'AL000984', '1899-12-30', '10:57:00')
insert into @t values(17 ,'BL000985', '1899-12-30', '11:06:00')
insert into @t values(18 ,'BL000985', '1899-12-30', '11:14:00')
insert into @t values(44 ,'BL000985', '1899-12-30', '11:24:00')
insert into @t values(50 ,'BL000985', '1899-12-30', '11:36:00')
insert into @t values(51 ,'BL000985', '1899-12-30', '11:46:00')
select
*,
datediff(n,(select thetime from @t where ID=(select max(ID) from @t where ID <t.ID)),thetime) as '分差'
from
@t t
/*
ID UserID thedate thetime 分差
----------- ---------- ---------- ---------- -----------
1 AL000984 1899-12-30 10:41:00 NULL
5 AL000984 1899-12-30 10:42:00 1
6 AL000984 1899-12-30 10:46:00 4
14 AL000984 1899-12-30 10:57:00 11
17 BL000985 1899-12-30 11:06:00 9
18 BL000985 1899-12-30 11:14:00 8
44 BL000985 1899-12-30 11:24:00 10
50 BL000985 1899-12-30 11:36:00 12
51 BL000985 1899-12-30 11:46:00 10
(9 row(s) affected)
*/