CREATE TABLE `sys_log` (
`Time_out` datetime DEFAULT NULL,
`LOG_ID` int(11) NOT NULL AUTO_INCREMENT,
`USER_ID` varchar(20) DEFAULT NULL,
`TIME` datetime DEFAULT NULL,
`IP` varchar(20) DEFAULT NULL,
`IP_AREA` varchar(225) DEFAULT NULL,
`TYPE` varchar(10) DEFAULT '1',
`REMARK` mediumtext NOT NULL,
PRIMARY KEY (`LOG_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=25451 DEFAULT CHARSET=utf8;
INSERT INTO `sys_log` VALUES ('2010-10-14 15:46:57', '1', 'admin', '2010-10-14 14:46:57', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-14 16:46:57', '2', 'admin', '2010-10-14 16:20:16', '127.0.0.1', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-14 18:46:57', '3', 'admin', '2010-10-14 17:56:35', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-14 20:46:57', '4', 'admin', '2010-10-14 19:00:59', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-15 16:46:57', '5', 'admin', '2010-10-15 16:09:44', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-16 10:46:57', '6', 'admin', '2010-10-16 09:02:22', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-16 14:46:57', '7', 'admin', '2010-10-16 12:48:24', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-16 10:46:57', '9', 'tom', '2010-10-16 09:02:22', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-17 14:46:57', '10', 'tom', '2010-10-17 12:48:24', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-18 10:46:57', '11', 'tom', '2010-10-18 09:02:22', '122.193.135.84', null, '1', '');求2011-10-01到2011-10-30日之间所有人的未登陆的天数及平均的每天的登录时间
结果如下 姓名 未登录天数 平均登陆时间
admin 27天 0.3小时
tom 28天 0.1小时如admin的未登录天数=2011-10-01到2011-10-30的30天减去14,15,16登陆过的3天
admin的平均登陆时间=每天的 time_out-time的时间相加(所有在线时间) 除以30天mysql 5数据库
`Time_out` datetime DEFAULT NULL,
`LOG_ID` int(11) NOT NULL AUTO_INCREMENT,
`USER_ID` varchar(20) DEFAULT NULL,
`TIME` datetime DEFAULT NULL,
`IP` varchar(20) DEFAULT NULL,
`IP_AREA` varchar(225) DEFAULT NULL,
`TYPE` varchar(10) DEFAULT '1',
`REMARK` mediumtext NOT NULL,
PRIMARY KEY (`LOG_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=25451 DEFAULT CHARSET=utf8;
INSERT INTO `sys_log` VALUES ('2010-10-14 15:46:57', '1', 'admin', '2010-10-14 14:46:57', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-14 16:46:57', '2', 'admin', '2010-10-14 16:20:16', '127.0.0.1', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-14 18:46:57', '3', 'admin', '2010-10-14 17:56:35', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-14 20:46:57', '4', 'admin', '2010-10-14 19:00:59', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-15 16:46:57', '5', 'admin', '2010-10-15 16:09:44', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-16 10:46:57', '6', 'admin', '2010-10-16 09:02:22', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-16 14:46:57', '7', 'admin', '2010-10-16 12:48:24', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-16 10:46:57', '9', 'tom', '2010-10-16 09:02:22', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-17 14:46:57', '10', 'tom', '2010-10-17 12:48:24', '122.193.135.84', null, '1', '');
INSERT INTO `sys_log` VALUES ('2010-10-18 10:46:57', '11', 'tom', '2010-10-18 09:02:22', '122.193.135.84', null, '1', '');求2011-10-01到2011-10-30日之间所有人的未登陆的天数及平均的每天的登录时间
结果如下 姓名 未登录天数 平均登陆时间
admin 27天 0.3小时
tom 28天 0.1小时如admin的未登录天数=2011-10-01到2011-10-30的30天减去14,15,16登陆过的3天
admin的平均登陆时间=每天的 time_out-time的时间相加(所有在线时间) 除以30天mysql 5数据库
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货