求一个用户访问序号的生成问题，分不多请大家帮忙

Question

表1：
uid product optiontime
1001 F1 2011-10-01 09:00:00.000
1001 F2 2011-10-01 09:01:00.000
1001 F3 2011-10-01 09:02:00.000
1001 F1 2011-10-01 17:00:00.000
1001 F3 2011-10-01 17:01:00.000
1001 F1 2011-10-02 09:01:00.000
1002 F1 2011-10-01 10:00:00.000表2：
uid product optiontime          ordernumber
1001 F1 2011-10-01 09:00:00.000   1
1001 F2 2011-10-01 09:01:00.000   2
1001 F3 2011-10-01 09:02:00.000   3
1001 F1 2011-10-01 17:00:00.000   1
1001 F3 2011-10-01 17:01:00.000   2
1001 F1 2011-10-02 09:01:00.000   1
1002 F1 2011-10-01 10:00:00.000   1    原表1是用户登陆产品后对产品中各功能的访问记录。
    问题是一个用户可能一天之内多次登陆产品，这样每次登陆产品后就会不同的访问顺序，这里可能需要解决两个问题：
    第一，如何辨识用户每次的登陆行为（规则：前后两条记录时间差在30分钟之内的都算是属于同一次的登陆行为）。
    第二，识别出用户每次的登陆行为后如何给它用户序号标识出顺序。
    最终结果要得到表2

Answer 1

create table tb(uid varchar(10),product varchar(10),optiontime datetime)
insert into tb select '1001','F1','2011-10-01 09:00:00.000'
insert into tb select '1001','F2','2011-10-01 09:01:00.000'
insert into tb select '1001','F3','2011-10-01 09:02:00.000'
insert into tb select '1001','F1','2011-10-01 17:00:00.000'
insert into tb select '1001','F3','2011-10-01 17:01:00.000'
insert into tb select '1001','F1','2011-10-02 09:01:00.000'
insert into tb select '1002','F1','2011-10-01 10:00:00.000'
go
;with c1 as(
select row_number()over(partition by uid order by optiontime)rn,* from tb
),c2 as(
select *,1 as ordernumber from c1 a 
where not exists(select 1 from c1 where uid=a.uid and optiontime<a.optiontime and datediff(mi,optiontime,a.optiontime)<30)
union all
select b.*,a.ordernumber+1 from c2 a inner join c1 b on a.uid=b.uid and a.rn=b.rn-1 and datediff(mi,a.optiontime,b.optiontime)<30
)select uid,product,optiontime,ordernumber from c2 order by uid,optiontime
/*
uid        product    optiontime              ordernumber
---------- ---------- ----------------------- -----------
1001       F1         2011-10-01 09:00:00.000 1
1001       F2         2011-10-01 09:01:00.000 2
1001       F3         2011-10-01 09:02:00.000 3
1001       F1         2011-10-01 17:00:00.000 1
1001       F3         2011-10-01 17:01:00.000 2
1001       F1         2011-10-02 09:01:00.000 1
1002       F1         2011-10-01 10:00:00.000 1(7 行受影响)*/
go
drop table tb

Answer 2

--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (uid int,product varchar(2),optiontime datetime)
insert into [tb]
select 1001,'F1','2011-10-01 09:00:00.000' union all
select 1001,'F2','2011-10-01 09:01:00.000' union all
select 1001,'F3','2011-10-01 09:02:00.000' union all
select 1001,'F1','2011-10-01 17:00:00.000' union all
select 1001,'F3','2011-10-01 17:01:00.000' union all
select 1001,'F1','2011-10-02 09:01:00.000' union all
select 1002,'F1','2011-10-01 10:00:00.000'--开始查询
declare @n int=1,@m int=1,@flag int,@cnt int;with cte as(
select rid=row_number() over (order by uid,optiontime),* from tb
)
select a.*,flag=b.rid
into #temptb
from cte a left join cte b on (a.uid=b.uid and a.rid=b.rid+1 and DATEDIFF(MINUTE,b.optiontime,a.optiontime)<=30)set @cnt=@@ROWCOUNT
while @m<=@cnt
begin
update #temptb set flag=@n where rid=@m
set @m=@m+1
set @n=@n+1

select @flag=flag from #temptb where rid=@m
if @flag is null
set @n=1
endselect * from #temptb--结束查询
drop table #temptb,[tb]/*
rid                  uid         product optiontime              flag
-------------------- ----------- ------- ----------------------- --------------------
1                    1001        F1      2011-10-01 09:00:00.000 1
2                    1001        F2      2011-10-01 09:01:00.000 2
3                    1001        F3      2011-10-01 09:02:00.000 3
4                    1001        F1      2011-10-01 17:00:00.000 1
5                    1001        F3      2011-10-01 17:01:00.000 2
6                    1001        F1      2011-10-02 09:01:00.000 1
7                    1002        F1      2011-10-01 10:00:00.000 1(7 行受影响)