select a.neme,a.time 上线时间,b.time 在线时间 from
(select neme,time from table1 where status='in') a,
(select neme,time from table1 where status='out') b
where a.neme=b.neme
(select neme,time from table1 where status='in') a,
(select neme,time from table1 where status='out') b
where a.neme=b.neme
(select name,time as intime from aa
where status = 'in') a,(
select name ,time as outtime from aa
where status = 'out' ) b
where a.name = b.name and b.outtime > a.intime group by a.name,a.intime
order by a.name,a.intime
每次in后开始记录时间,到out为止;将in、out之间的时间在一段期段内累加。上面两个sql都不能实现这个目的。
用sql也不好实现,建议贴主还是创建存储过程比较容易。
具体思路是:
用游标读记录,对每个name从in开始,读到一个out时就把时间记录在变量里。
如果从out到in就不记时间,依次循环,输出结果。
(select a.name,a.time,(b.time-a.time) sj from
(select rownum rn,c.name,c.time from
(select name,time from table1 where status = 'in' order by name,id) c) a,
(select rownum rn,d.name,d.time from
(select name,time from table1 where status = 'out' order by name,id) d) b
where a.rn = b.rn and a.name=b.name) aa
where aa.time >= ..... and bb.time <= .....
group by aa.name;这个sql语句只适用于in和out一一对应的情况,既有in必须有out,才能统计准确。
其次他的效率我也不能向你保证,估计比蜗牛要快一点?!
(select name,time as intime from table1
where status = 'in') a,(
select name ,time as outtime from table1
where status = 'out' ) b
where a.name = b.name an b.outtime > a.intime group by a.name,a.intime
and a.intime between time1 and time2
order by a.name,a.intime
其中的time1和time2自己添
from table1 a,table1 b
where a.status='on' and b.status='off' and a.name=b.name
select a.name,a.上线时间,b.在线时间
from
(select name,min(time) as 上线时间 from table1 where status='in' group by name
) a,
(select name,Sum(outtime-intime) as 在线时间
from
(select a.name,a.intime,min(b.outtime) as outtime from
(select name,time as intime from table1
where status = 'in') a,
(select name ,time as outtime from table1
where status = 'out' ) b
where a.name = b.name and b.outtime > a.intime
group by a.name,a.intime
)
group by name) b
where a.name=b.name
比如dd 是in状态,到目前当前时间为止,它还是in的状态,
那你的在线时间又怎么计算呢
select a.name,a.intime,min(b.outtime) ,min(b.outtime)-a.intime as onlinetime from
(select name,time as intime from table1
where status = 'in') a,(
select name ,time as outtime from table1
where status = 'out' ) b
where a.name = b.name an b.outtime > a.intime
and a.intime between time1 and time2
group by a.name,a.intime
order by a.name,a.intime
可以在上面的SQL语句前加上如下的PL-SQL:
(让单记录的Name,即有in而没有out的情况,变成双记录。即增加一条outTime为当前时间的记录)declare
tID number(10);
cursor mycursor is
select name,count(*) tcount from test group by name;
begin
for row_Test in mycursor loop
select max(id)+1 into tID from test;
if row_Test.tcount mod 2=1 then
insert into table1 values(tID,row_Test.name,sysdate,'out');
end if;
end loop;
commit;
end;
纠正一下:
上面的所有表名‘test’更正为‘table1’.
我综合了一下,各大虾的 写了如下的
select
name,min(intime),outtime
from (
select name,intime,min(outtime) outtime
from
(select a.name,a.intime,b.outtime from
(select name,time as intime from test where status='in' ) a,
(select name,time as outtime from test where status='out' ) b
where a.name=b.name and a.intime<b.outtime
)
group by name,intime
) group by name,outtime这样解决了 当某个name 连续两个in的情况,认为是在线的状态。
但问题是 我想做的是某个时间段内 用户的在线时间,现在存在以下的几个问题:
1.在begin time时间后的一个状态为out,认为该时间段为用户的在线时间,
应该计算在内。
2.当最后一个状态为in的时候认为该时间段为用户的在线时间,应该计算在内。
难啊?各位大虾能否再出出注意拜托了!
我有4个疑问
1。某个Name可能有连续两个in却一个out都没有吗?
2。你说的问题1是什么意思?
3。你说的问题2在我补充的那段pl/sql不就可以解决了吗?
4。我认为你综合的那段SQL有问题。
2 目前登陆可以没有out,