开发者

Need to find current connections in mysql media server log table

I have a media server logging to a mysql database and it records a seperate row for each connect, play, stop, disconnect event. What I would like to do is find connect events that do not have a related disconnect event or play events that do not have a related stop event.

date          time        category    event      clientId    stream    streamId
===============================================================================
2010-04-21    10:30:00    session     connect    1          
2010-04-21    10:30:05    stream      start      1           stream1    1
2010-04-21    10:35:00    stream      stop       1           stream1    1
2010-04-21    10:35:00    session     disconnect 1           
2010-04-21    10:35:00    session     connect    2           
2010-04-21    10:35:05    stream      start      2           stream2    1
2010-04-21    10:35:08    session     connect    3           
2010-04-21    10:35:13    stream      start      3           stream1    1
2010-04-21    10:37:05    stream      stop       2           stream2    1
2010-04-21    10:37:10    stre开发者_开发问答am      start      2           stream1    2

I would like to be able to get a total of current sessions and in a seperate query, a total of current streams being played.

Thanks,

Roger.


I have ended up using a compound select statement.

select * from (select *, (select true from statslog as t1 where t1.clientId = statslog.clientId and t1.event = 'disconnect') as `disconnected`
from statslog where statslog.category = 'session' and statslog.event = 'connect') as t2 where isnull(t2.disconnected);
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜