关于mysql:统计在线时长

—— 表构造 ——
CREATE TABLE usr_online_log (
id int NOT NULL AUTO_INCREMENT COMMENT ‘ 自增 id’,
usr_id int NOT NULL COMMENT ‘ 用户 id’,
is_online tinyint DEFAULT ‘1’ COMMENT ‘0- 下线，1- 上线 ’,
creat_time datetime DEFAULT NULL COMMENT ‘ 创立工夫 ’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’ 用户登录在线记录表 ’;

—–sql 语句 ——–
SELECT
usr_id,
DATE_FORMAT(DATE_ADD(
‘1900-01-01 00:00:00’,
interval sum(interrval_time) second
),’%T’) AS total_interval,
creat_time date
FROM
(
SELECT
t1.usr_id,
LEFT (t1.creat_time, 10) AS creat_time,
TIMESTAMPDIFF(SECOND,t1.creat_time,t2.creat_time) AS interrval_time
FROM
usr_online_log t1
INNER JOIN usr_online_log t2 ON t1.usr_id = t2.usr_id
AND t1.is_online = 1
AND t2.is_online = 0
AND t1.creat_time < t2.creat_time
AND t2.id = (
SELECT min(id) FROM usr_online_log
WHERE usr_id = t1.usr_id
AND is_online = 0
AND id > t1.id
)
) a
GROUP BY
usr_id,
creat_time