表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是离线时间
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是离线时间
解决方案 »
- 普通的关系型数据库用的是什么类型的数据结构?
- 奇怪?往sql server的某个表中粘贴数据,怎么只能粘贴一部分呢?
- Sql2005可以安装在winXP+sp2系统?????????
- 在一台机器上生成了DTS的.dts文件(可以正常运行),请问,如何把这个包安装到另一个SQL SERVER中,并存在SQL SERVER中?
- 动态字段
- 如何实现一台机器更新数据,其他机器都能看到?
- 用一个表的字段更新同一个表中的另一个字段
- xml 更新!
- 告急:海量数据插入
- 怎么在win98下安装oracle8.05?
- 【急!求救!!】设置了push subscription,又想更改表字段怎么办?提示说:发布的表不能更改,只能删除复制,然后重建复制吗?
- 问一个关于sql数据库的问题
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
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
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
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
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
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
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
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