求一存贮过程！

[Hegemon]

表A
F_loginid F_logintime F_name F_Event(1=登录，0=离线)
0001 2008-04-03 14:40:04 aaa 1
0001 2008-04-03 14:40:59 aaa 0
0002 2008-04-03 14:42:09 aaa 1
0002 2008-04-03 14:44:13 aaa 0

要统计在线时长
统计结果如下：F_name F_logintime F_leveltime F_ontimes
aaa 2008-04-03 14:40:04 2008-04-03 14:40:59 00:00:55
aaa 2008-04-03 14:42:09 2008-04-03 14:44:13 00:02:04

F_Event=1 时F_logintime是登录时间
F_event=0 时F_logintime是离线时间

[-狙击手-]

select f_loginid,
       f_name,
       max(case when f_event = 1 then f_logintime else null end) as f_logintime,
       max(case when f_event = 0 then f_logintime else null end) as f_logintime,
        max(case when f_event = 1 then f_logintime else null end) as f_logintime -
       max(case when f_event = 0 then f_logintime else null end) as f_logintime,
from ta
group by f_loginid,f_name

[liangCK]

select f_name,
max(case when f_event=0 then f_logintime end) f_logintime,
max(case when f_event=1 then f_logintime end) f_leveltime,
max(case when f_event=1 then f_logintime end)-max(case when f_event=0 then f_logintime end) f_ontimes
from tb
group by f_loginid,f_name

[liangCK]

[Quote=引用 1 楼 happyflystone 的回复:]
SQL codeselect f_loginid,
f_name,
max(case when f_event = 1 then f_logintime else null end) as f_logintime,
max(case when f_event = 0 then f_logintime else null end) as f_logintime,
max(case when f_event = 1 then f_logintime else null end) as f_logintime -
max(case when f_event = 0 then f_logintime else null end) as f_logintime,
from ta
group by f_login…
[/Quote]

BS盗版

[-狙击手-]

select f_loginid,
f_name,
max(case when f_event = 1 then f_logintime else null end) as f_logintime,
max(case when f_event = 0 then f_logintime else null end) as F_leveltime ,
max(case when f_event = 1 then f_logintime else null end) -
max(case when f_event = 0 then f_logintime else null end) as f_ontimes ,
from ta
group by f_loginid,f_name

[utpcb]

他们写的简单了点吧！　如果楼住的里面登陆的同一个ＩＤ有多个登陆时间呢多个离线时间呢？　楼住你要自己慢慢看写一个完整的！

[pt1314917]

declare @t table(F_loginid varchar(10),F_logintime datetime,F_name varchar(10),F_Event int)
insert into @t select '0001','2008-04-03 14:40:04','aaa',1
insert into @t select '0001','2008-04-03 14:40:59','aaa',0
insert into @t select '0002','2008-04-03 14:42:09','aaa',1
insert into @t select '0002','2008-04-03 14:44:13','aaa',0


select a.f_name,a.F_logintime,b.F_logintime,
F_ontimes=convert(varchar(10),b.F_logintime-a.F_logintime,108)
from @t a,@t b
where a.f_name=b.f_name and a.F_loginid=b.F_loginid and b.f_event=0 and a.f_event=1

[sweetweiwei]

create procedure dbo.getA
as
select F_name,
F_logintime,
F_leveltime,
F_ontimes
from (select F_loginid,
F_name,
min(F_logintime) as F_logintime,
max(F_logintime) as F_leveltime,
max(F_logintime) - min(F_logintime) as F_ontimes
from A
group by F_loginid,F_name) t
go

[sweetweiwei]

create procedure dbo.getA
as
select F_name,
F_logintime,
F_leveltime,
F_ontimes
from (select F_loginid,
F_name,
min(F_logintime) as F_logintime,
max(F_logintime) as F_leveltime,
max(F_logintime) - min(F_logintime) as F_ontimes
from A
group by F_loginid,F_name) t
go

[让你望见影子的墙]

[Quote=引用 5 楼 utpcb 的回复:]
他们写的简单了点吧！　如果楼住的里面登陆的同一个ＩＤ有多个登陆时间呢多个离线时间呢？　楼住你要自己慢慢看写一个完整的！
[/Quote]
同意啊。如果一个用户登陆时，上一次的登陆信息还要不要保存呢？

[橘子香水]

select a.F_name,a.F_logintime,F_leveltime=b.F_logintime,
F_ontimes=b.F_logintime-a.F_logintime
from table a,table b
where a.F_Event=1 and b.F_Event=0 and
a.F_loginid=b.F_loginid

[bote_china]

select 
	A.F_loginid,
	A.F_logintime,
	(select  
		min(F_logintime) 
	 from A B 
	 where B.F_Event=0 and B.F_loginid =A.F_loginid 
					   and B.F_logintime>A.F_logintime)
	AS F_LogoutTime,
	F_name ,
	F_event 
from A 
where f_event =1
order by F_loginid

[表和俺说你很纯洁]

学习

[kaikai_kk]

[Quote=引用 9 楼 HEROWANG 的回复:]
引用 5 楼 utpcb 的回复:
他们写的简单了点吧！　如果楼住的里面登陆的同一个ＩＤ有多个登陆时间呢多个离线时间呢？　楼住你要自己慢慢看写一个完整的！

同意啊。如果一个用户登陆时，上一次的登陆信息还要不要保存呢？
[/Quote]


LZ现在的数据,好像就是同一用户aaa,登陆两次啊