MySQL count Total Logged In Time for Application

---

---

---

For every login

find the next logout (later logout with minimum distance)

find the next login (later login with minimum distance)

if next login is before next logout, then ignore the login record

Here is the statement. I hope it works:

select 
  userId,
  sum(logintimeinhour) as totallogintimeinhour
from
(
  select 
    login.userid, 
    min(time_to_sec(timediff(ifnull(later_logout.time,now()), login.time)))/(60*60) as loginTimeInHour
  from EventLog login
  left join EventLog later_login 
    on later_login.eventType = 'LOGIN' 
    and later_login.userId = login.userId
    and later_login.time > later_login.time
  left join EventLog later_logout 
    on later_logout.eventType = 'LOGOUT' 
    and later_logout.userId = login.userId
    and later_logout.time > later_login.time
  where login.eventType = 'LOGIN'
  group by login.id
  having
    min( ifnull(later_logout.time,now()) ) <=
    min( ifnull(later_login.time,now()) )
)
group by userid
order by userid;

链接地址: http://www.djcxy.com/p/95050.html

上一篇: 使用where语句引导MySQL查询

下一篇: MySQL计数应用程序的总登录时间