mysql 日志查询 登录时长统计

CREATE TABLE `login_log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` int(11) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `member_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;


INSERT INTO `login_log` (`id`, `type`, `create_time`, `member_id`)
VALUES
    (1, 1, '2018-04-04 08:20:00', 1001),
    (2, 1, '2018-04-04 08:21:00', 1001),
    (3, 1, '2018-04-04 08:23:00', 1001),
    (4, 2, '2018-04-04 09:23:00', 1001),
    (5, 1, '2018-04-04 12:20:00', 1001),
    (6, 1, '2018-04-04 12:30:00', 1001),
    (7, 2, '2018-04-04 13:30:00', 1001),
    (8, 1, '2018-04-04 08:20:00', 1002),
    (9, 1, '2018-04-04 08:20:00', 1002),
    (10, 1, '2018-04-04 08:20:00', 1002),
    (11, 2, '2018-04-04 08:20:00', 1002),
    (12, 1, '2018-04-04 08:20:00', 1002),
    (13, 1, '2018-04-04 08:20:00', 1002),
    (14, 2, '2018-04-04 08:30:30', 1002);

select  

sum(oid.create_time - (SELECT iid.create_time from login_log iid where `type`=1 and oid.id >iid.id  and iid.member_id=oid.member_id limit 1) ) as logined_time, member_id

from login_log as oid 

where `type`=2 

group by oid.member_id ;

发表评论

电子邮件地址不会被公开。 必填项已用*标注