[code=SQL]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 = 1
union all
select 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
) n
where 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 0
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 0
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
*/[/code]
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 = 1
union all
select 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
) n
where 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 0
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 0
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
*/[/code]
create table tb(ID int,UserID varchar(10),thedatetime datetime)
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.thedatetime,[minute] = 0 from
(
select px=(select count(1) from tb where UserID=a.UserID and thedatetime<a.thedatetime)+1 , * from tb a
) t where px = 1
union all
select n.id,n.UserID,n.thedatetime,[minute]=datediff(minute,m.thedatetime,n.thedatetime) from
(
select px=(select count(1) from tb where UserID=a.UserID and thedatetime<a.thedatetime)+1 , * from tb a
) m,
(
select px=(select count(1) from tb where UserID=a.UserID and thedatetime<a.thedatetime)+1 , * from tb a
) n
where m.UserID = n.UserID and m.px = n.px - 1
order by t.userid,t.thedatetimedrop table tb/*
id UserID thedatetime minute
----------- ---------- ------------------------------------------------------ -----------
1 AL000984 1899-12-30 10:41:00.000 0
5 AL000984 1899-12-30 10:42:00.000 1
6 AL000984 1899-12-30 10:46:00.000 4
14 AL000984 1899-12-30 10:57:00.000 11
17 BL000985 1899-12-30 11:06:00.000 0
18 BL000985 1899-12-30 11:14:00.000 8
44 BL000985 1899-12-30 11:24:00.000 10
50 BL000985 1899-12-30 11:36:00.000 12
51 BL000985 1899-12-30 11:46:00.000 10(所影响的行数为 9 行)
*/
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')
select
*,
m=datediff(mi,(select max(thedatetime) from tb where UserID=a.UserID and id<a.id),a.thedatetime)
from
tb a
ID UserID thedatetime m
----------- ---------- ------------------------------------------------------ -----------
1 AL000984 1899-12-30 10:41:00.000 NULL
5 AL000984 1899-12-30 10:42:00.000 1
6 AL000984 1899-12-30 10:46:00.000 4
14 AL000984 1899-12-30 10:57:00.000 11
17 BL000985 1899-12-30 11:06:00.000 NULL
18 BL000985 1899-12-30 11:14:00.000 8
44 BL000985 1899-12-30 11:24:00.000 10
50 BL000985 1899-12-30 11:36:00.000 12
51 BL000985 1899-12-30 11:46:00.000 10(所影响的行数为 9 行)
如果不想看到null 的话,加上isnull(m,0) 就好了
create table tb(ID int,UserID varchar(10),thedatetime datetime)
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')
go
select *,[minute] = isnull(datediff(minute,(select max(thedatetime) from tb where UserID=a.UserID and thedatetime <a.thedatetime),a.thedatetime),0) from tb a drop table tb/*
ID UserID thedatetime minute
----------- ---------- ------------------------------------------------------ -----------
1 AL000984 1899-12-30 10:41:00.000 0
5 AL000984 1899-12-30 10:42:00.000 1
6 AL000984 1899-12-30 10:46:00.000 4
14 AL000984 1899-12-30 10:57:00.000 11
17 BL000985 1899-12-30 11:06:00.000 0
18 BL000985 1899-12-30 11:14:00.000 8
44 BL000985 1899-12-30 11:24:00.000 10
50 BL000985 1899-12-30 11:36:00.000 12
51 BL000985 1899-12-30 11:46:00.000 10(所影响的行数为 9 行)
*/