create table T(id int,order_id int,sid int,modify_date datetime)insert into t select 6,21,1,'2010-10-01 11:05:55.000'
insert into t select 7,21,2,'2010-10-02 11:07:02.000'
insert into t select 8,21,3,'2010-10-03 12:49:17.000'
insert into t select 9,21,4,'2010-10-04 13:50:56.000'
insert into t select 10,21,5,'2010-10-04 15:52:50.000'
insert into t select 11,72,1,'2010-11-01 11:05:55.000'
insert into t select 12,72,2,'2010-11-02 08:07:02.000'
insert into t select 13,72,3,'2010-11-02 09:10:17.000'
insert into t select 14,72,4,'2010-11-03 10:12:56.000'
insert into t select 15,72,5,'2010-11-04 12:16:50.000'根据sid排序;
按order_id分类,sid最小的h为0最后得到结果6 21 1 2010-10-01 11:05:55.000 0
7 21 2 2010-10-02 11:07:02.000 24
8 21 3 2010-10-03 12:49:17.000 25
9 21 4 2010-10-04 13:50:56.000 25
10 21 5 2010-10-04 15:52:50.000 2
11 72 1 2010-11-01 11:05:55.000 0
12 72 2 2010-11-02 08:07:02.000 21
13 72 3 2010-11-02 09:10:17.000 1
14 72 4 2010-11-03 10:12:56.000 25
15 72 5 2010-11-04 12:16:50.000 26
insert into t select 7,21,2,'2010-10-02 11:07:02.000'
insert into t select 8,21,3,'2010-10-03 12:49:17.000'
insert into t select 9,21,4,'2010-10-04 13:50:56.000'
insert into t select 10,21,5,'2010-10-04 15:52:50.000'
insert into t select 11,72,1,'2010-11-01 11:05:55.000'
insert into t select 12,72,2,'2010-11-02 08:07:02.000'
insert into t select 13,72,3,'2010-11-02 09:10:17.000'
insert into t select 14,72,4,'2010-11-03 10:12:56.000'
insert into t select 15,72,5,'2010-11-04 12:16:50.000'根据sid排序;
按order_id分类,sid最小的h为0最后得到结果6 21 1 2010-10-01 11:05:55.000 0
7 21 2 2010-10-02 11:07:02.000 24
8 21 3 2010-10-03 12:49:17.000 25
9 21 4 2010-10-04 13:50:56.000 25
10 21 5 2010-10-04 15:52:50.000 2
11 72 1 2010-11-01 11:05:55.000 0
12 72 2 2010-11-02 08:07:02.000 21
13 72 3 2010-11-02 09:10:17.000 1
14 72 4 2010-11-03 10:12:56.000 25
15 72 5 2010-11-04 12:16:50.000 26
insert into t select 7,21,2,'2010-10-02 11:07:02.000'
insert into t select 8,21,3,'2010-10-03 12:49:17.000'
insert into t select 9,21,4,'2010-10-04 13:50:56.000'
insert into t select 10,21,5,'2010-10-04 15:52:50.000'
insert into t select 11,72,1,'2010-11-01 11:05:55.000'
insert into t select 12,72,2,'2010-11-02 08:07:02.000'
insert into t select 13,72,3,'2010-11-02 09:10:17.000'
insert into t select 14,72,4,'2010-11-03 10:12:56.000'
insert into t select 15,72,5,'2010-11-04 12:16:50.000'select m.* , isnull(datediff(hh,(select top 1 modify_date from t where order_id = m.order_id and sid < m.sid order by sid desc),modify_date),0) from t mdrop table t/*
id order_id sid modify_date
----------- ----------- ----------- ------------------------------------------------------ -----------
6 21 1 2010-10-01 11:05:55.000 0
7 21 2 2010-10-02 11:07:02.000 24
8 21 3 2010-10-03 12:49:17.000 25
9 21 4 2010-10-04 13:50:56.000 25
10 21 5 2010-10-04 15:52:50.000 2
11 72 1 2010-11-01 11:05:55.000 0
12 72 2 2010-11-02 08:07:02.000 21
13 72 3 2010-11-02 09:10:17.000 1
14 72 4 2010-11-03 10:12:56.000 25
15 72 5 2010-11-04 12:16:50.000 26(所影响的行数为 10 行)
*/
create table T(id int,order_id int,sid int,modify_date datetime)
insert into t select 6,21,1,'2010-10-01 11:05:55.000'
insert into t select 7,21,2,'2010-10-02 11:07:02.000'
insert into t select 8,21,3,'2010-10-03 12:49:17.000'
insert into t select 9,21,4,'2010-10-04 13:50:56.000'
insert into t select 10,21,5,'2010-10-04 15:52:50.000'
insert into t select 11,72,1,'2010-11-01 11:05:55.000'
insert into t select 12,72,2,'2010-11-02 08:07:02.000'
insert into t select 13,72,3,'2010-11-02 09:10:17.000'
insert into t select 14,72,4,'2010-11-03 10:12:56.000'
insert into t select 15,72,5,'2010-11-04 12:16:50.000'select t1.*,
DATEDIFF(HH,
isnull((select top 1 modify_date
from T
where sid<t1.sid and order_id=t1.order_id
order by sid desc),modify_date),
modify_date) h
from T t1;drop table T;
(
Select a.*,b.* from
(Select id1=row_number() over (order by sid),id,order_id,sid,modify_date from #T) a,
(Select id2=row_number() over (order by sid),modify_date dd from #T) b Where a.id1=b.id2-1
)
Select * from fff
自己再计算吧!后面也比较简单!