先附上创建表的脚本和数据CREATE TABLE `sys_user_journal` (
`ID` int(11) NOT NULL auto_increment,
`USER_ID` int(11) NOT NULL,
`BUSINESS_ID` varchar(200) NOT NULL,
`CREATE_TIME` datetime NOT NULL,
`DESCRIPTION` varchar(200) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='系统用户日志表';
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (1, 'editGroup', '2009-08-19 13:21:12', '权限组ID『2』修改成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (1, 'editGroup', '2009-08-19 13:32:15', '权限组ID『2』修改成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (1, 'editGroup', '2009-08-19 16:24:33', '权限组ID『2』修改成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (365, 'login', '2009-08-20 10:31:15', '用户『古龙12』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (384, 'login', '2009-08-20 10:31:15', '用户『古龙31』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (398, 'logout', '2009-08-20 10:31:15', '用户『古龙45』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (397, 'login', '2009-08-20 10:31:16', '用户『古龙44』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (419, 'login', '2009-08-20 10:31:16', '用户『古龙66』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (365, 'logout', '2009-08-20 10:31:16', '用户『古龙12』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (382, 'login', '2009-08-20 10:31:16', '用户『古龙29』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (373, 'logout', '2009-08-20 10:31:17', '用户『古龙20』退出系统');
INSERT INTO `sys_user_journalvVALUES (429, 'logout', '2009-08-20 10:31:17', '用户『古龙76』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (419, 'logout', '2009-08-20 10:31:17', '用户『古龙66』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (421, 'login', '2009-08-20 10:31:17', '用户『古龙68』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (420, 'login', '2009-08-20 10:31:17', '用户『古龙67』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (382, 'logout', '2009-08-20 10:31:17', '用户『古龙29』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (397, 'logout', '2009-08-20 10:31:18', '用户『古龙44』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (371, 'login', '2009-08-20 10:31:18', '用户『古龙18』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'login', '2009-08-20 10:31:18', '用户『古龙85』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (421, 'logout', '2009-08-20 10:31:19', '用户『古龙68』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (409, 'login', '2009-08-20 10:31:19', '用户『古龙56』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (420, 'logout', '2009-08-20 10:31:20', '用户『古龙67』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (371, 'logout', '2009-08-20 10:31:20', '用户『古龙18』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (375, 'login', '2009-08-20 10:31:20', '用户『古龙22』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'logout', '2009-08-20 10:31:20', '用户『古龙85』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (409, 'logout', '2009-08-20 10:31:20', '用户『古龙56』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (448, 'logout', '2009-08-20 10:31:20', '用户『古龙95』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (424, 'login', '2009-08-20 10:31:20', '用户『古龙71』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (443, 'logout', '2009-08-20 10:31:20', '用户『古龙90』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'logout', '2009-08-20 10:31:21', '用户『古龙85』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (424, 'logout', '2009-08-20 10:31:21', '用户『古龙71』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (375, 'logout', '2009-08-20 10:31:21', '用户『古龙22』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (452, 'login', '2009-08-20 10:31:22', '用户『古龙99』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (417, 'login', '2009-08-20 10:31:22', '用户『古龙64』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (447, 'login', '2009-08-20 10:31:23', '用户『古龙94』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (394, 'login', '2009-08-20 10:31:24', '用户『古龙41』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (452, 'logout', '2009-08-20 10:31:24', '用户『古龙99』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (444, 'login', '2009-08-20 10:31:24', '用户『古龙91』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (447, 'logout', '2009-08-20 10:31:24', '用户『古龙94』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (358, 'login', '2009-08-20 10:31:24', '用户『古龙5』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (395, 'login', '2009-08-20 10:31:24', '用户『古龙42』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (359, 'logout', '2009-08-20 10:31:24', '用户『古龙6』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (423, 'logout', '2009-08-20 10:31:24', '用户『古龙70』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'logout', '2009-08-20 10:31:24', '用户『古龙85』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (417, 'logout', '2009-08-20 10:31:25', '用户『古龙64』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (394, 'logout', '2009-08-20 10:31:25', '用户『古龙41』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (379, 'login', '2009-08-20 10:31:25', '用户『古龙26』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (434, 'login', '2009-08-20 10:31:25', '用户『古龙81』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (395, 'logout', '2009-08-20 10:31:25', '用户『古龙42』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'login', '2009-08-20 10:31:25', '用户『古龙85』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (358, 'logout', '2009-08-20 10:31:25', '用户『古龙5』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (444, 'logout', '2009-08-20 10:31:25', '用户『古龙91』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (428, 'login', '2009-08-20 10:31:25', '用户『古龙75』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (360, 'login', '2009-08-20 10:31:25', '用户『古龙7』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (386, 'login', '2009-08-20 10:31:26', '用户『古龙33』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (379, 'logout', '2009-08-20 10:31:26', '用户『古龙26』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (434, 'logout', '2009-08-20 10:31:26', '用户『古龙81』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (423, 'login', '2009-08-20 10:31:26', '用户『古龙70』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (360, 'logout', '2009-08-20 10:31:26', '用户『古龙7』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (384, 'login', '2009-08-20 10:31:26', '用户『古龙31』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (428, 'login', '2009-08-20 10:31:26', '用户『古龙75』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (428, 'logout', '2009-08-20 10:31:26', '用户『古龙75』退出系统');这张表是用户日志表,除了记录登录日志和登出日志,还有别的日志,
现在做压力测试,测试部让我配合在库表查看在线人数,
所以限制BUSINESS_ID为login或logout,
但系统允许重复登录,同一个用户id,没有登出,就可以再登录,
所以需要统计创建时间最晚的那条记录BUSINESS_ID是否是login,
按user_id分组,若CREATE_TIME最大的那条数据,BUSINESS_ID是login,
就符合条件
`ID` int(11) NOT NULL auto_increment,
`USER_ID` int(11) NOT NULL,
`BUSINESS_ID` varchar(200) NOT NULL,
`CREATE_TIME` datetime NOT NULL,
`DESCRIPTION` varchar(200) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='系统用户日志表';
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (1, 'editGroup', '2009-08-19 13:21:12', '权限组ID『2』修改成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (1, 'editGroup', '2009-08-19 13:32:15', '权限组ID『2』修改成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (1, 'editGroup', '2009-08-19 16:24:33', '权限组ID『2』修改成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (365, 'login', '2009-08-20 10:31:15', '用户『古龙12』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (384, 'login', '2009-08-20 10:31:15', '用户『古龙31』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (398, 'logout', '2009-08-20 10:31:15', '用户『古龙45』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (397, 'login', '2009-08-20 10:31:16', '用户『古龙44』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (419, 'login', '2009-08-20 10:31:16', '用户『古龙66』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (365, 'logout', '2009-08-20 10:31:16', '用户『古龙12』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (382, 'login', '2009-08-20 10:31:16', '用户『古龙29』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (373, 'logout', '2009-08-20 10:31:17', '用户『古龙20』退出系统');
INSERT INTO `sys_user_journalvVALUES (429, 'logout', '2009-08-20 10:31:17', '用户『古龙76』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (419, 'logout', '2009-08-20 10:31:17', '用户『古龙66』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (421, 'login', '2009-08-20 10:31:17', '用户『古龙68』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (420, 'login', '2009-08-20 10:31:17', '用户『古龙67』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (382, 'logout', '2009-08-20 10:31:17', '用户『古龙29』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (397, 'logout', '2009-08-20 10:31:18', '用户『古龙44』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (371, 'login', '2009-08-20 10:31:18', '用户『古龙18』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'login', '2009-08-20 10:31:18', '用户『古龙85』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (421, 'logout', '2009-08-20 10:31:19', '用户『古龙68』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (409, 'login', '2009-08-20 10:31:19', '用户『古龙56』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (420, 'logout', '2009-08-20 10:31:20', '用户『古龙67』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (371, 'logout', '2009-08-20 10:31:20', '用户『古龙18』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (375, 'login', '2009-08-20 10:31:20', '用户『古龙22』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'logout', '2009-08-20 10:31:20', '用户『古龙85』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (409, 'logout', '2009-08-20 10:31:20', '用户『古龙56』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (448, 'logout', '2009-08-20 10:31:20', '用户『古龙95』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (424, 'login', '2009-08-20 10:31:20', '用户『古龙71』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (443, 'logout', '2009-08-20 10:31:20', '用户『古龙90』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'logout', '2009-08-20 10:31:21', '用户『古龙85』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (424, 'logout', '2009-08-20 10:31:21', '用户『古龙71』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (375, 'logout', '2009-08-20 10:31:21', '用户『古龙22』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (452, 'login', '2009-08-20 10:31:22', '用户『古龙99』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (417, 'login', '2009-08-20 10:31:22', '用户『古龙64』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (447, 'login', '2009-08-20 10:31:23', '用户『古龙94』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (394, 'login', '2009-08-20 10:31:24', '用户『古龙41』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (452, 'logout', '2009-08-20 10:31:24', '用户『古龙99』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (444, 'login', '2009-08-20 10:31:24', '用户『古龙91』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (447, 'logout', '2009-08-20 10:31:24', '用户『古龙94』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (358, 'login', '2009-08-20 10:31:24', '用户『古龙5』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (395, 'login', '2009-08-20 10:31:24', '用户『古龙42』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (359, 'logout', '2009-08-20 10:31:24', '用户『古龙6』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (423, 'logout', '2009-08-20 10:31:24', '用户『古龙70』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'logout', '2009-08-20 10:31:24', '用户『古龙85』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (417, 'logout', '2009-08-20 10:31:25', '用户『古龙64』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (394, 'logout', '2009-08-20 10:31:25', '用户『古龙41』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (379, 'login', '2009-08-20 10:31:25', '用户『古龙26』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (434, 'login', '2009-08-20 10:31:25', '用户『古龙81』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (395, 'logout', '2009-08-20 10:31:25', '用户『古龙42』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (438, 'login', '2009-08-20 10:31:25', '用户『古龙85』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (358, 'logout', '2009-08-20 10:31:25', '用户『古龙5』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (444, 'logout', '2009-08-20 10:31:25', '用户『古龙91』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (428, 'login', '2009-08-20 10:31:25', '用户『古龙75』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (360, 'login', '2009-08-20 10:31:25', '用户『古龙7』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (386, 'login', '2009-08-20 10:31:26', '用户『古龙33』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (379, 'logout', '2009-08-20 10:31:26', '用户『古龙26』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (434, 'logout', '2009-08-20 10:31:26', '用户『古龙81』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (423, 'login', '2009-08-20 10:31:26', '用户『古龙70』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (360, 'logout', '2009-08-20 10:31:26', '用户『古龙7』退出系统');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (384, 'login', '2009-08-20 10:31:26', '用户『古龙31』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (428, 'login', '2009-08-20 10:31:26', '用户『古龙75』从『10.1.69.26』登录成功');
INSERT INTO `sys_user_journal`(USER_ID,BUSINESS_ID,CREATE_TIME,DESCRIPTION) VALUES (428, 'logout', '2009-08-20 10:31:26', '用户『古龙75』退出系统');这张表是用户日志表,除了记录登录日志和登出日志,还有别的日志,
现在做压力测试,测试部让我配合在库表查看在线人数,
所以限制BUSINESS_ID为login或logout,
但系统允许重复登录,同一个用户id,没有登出,就可以再登录,
所以需要统计创建时间最晚的那条记录BUSINESS_ID是否是login,
按user_id分组,若CREATE_TIME最大的那条数据,BUSINESS_ID是login,
就符合条件
select 1 from sys_user_journal b where b.sys_user_journal='login' and b.user_id=a.user_id and b.CREATE_TIME>a.CREATE_TIME)
left out join sys_user_journal s1
on s.USER_ID=s1.user_id
group by s.user_id
having s.CREATE_TIME<s1.CREATE_TIME
and s1.CREATE_TIME is not null;
t.*
from
`sys_user_journal` t
where
not exists(select 1 from `sys_user_journal` where user_id=t.user_id and BUSINESS_ID='login' and CREATE_TIME>t.CREATE_TIME)
and
BUSINESS_ID='login'
select * from sys_user_journal a where a.id=(select max(b.id) from sys_user_journal b where b.USER_ID=a.USER_ID) and a.sys_user_journal='login'
所以需要统计创建时间最晚的那条记录BUSINESS_ID是否是login, --这个不太明白,是要得到BUSINESS_ID是否是login?如果是这样,在后面加上and BUSINESS_ID='login'就可以了。
from sys_user_journal a
where CREATE_TIME <= '$date' and BUSINESS_ID ="login" ---在这个时间这之登陆的所有人
and USER_ID not in (select USER_ID from sys_user_journal where CREATE_TIME<=$date and < CREATE_TIME > a.CREATE_TIME and BUSINESS_ID ="logout") ----在这个时间之前本次登陆之后没登出
写法1:
select * from sys_user_journal a where a.sys_user_journal='login' and not exists (
select 1 from sys_user_journal b where b.sys_user_journal='login' and b.user_id=a.user_id and b.id>a.id)写法2:
select * from sys_user_journal a where a.id=(select max(b.id) from sys_user_journal b where b.USER_ID=a.USER_ID) and a.sys_user_journal='login'写法3:
select * from sys_user_journal a,(select USER_ID,max(id) as newid from sys_user_journal group by USER_ID) b where a.id=b.newid and a.sys_user_journal='login'
+----+---------+-------------+---------------------+----------------------------------------+
| ID | USER_ID | BUSINESS_ID | CREATE_TIME | DESCRIPTION |
+----+---------+-------------+---------------------+----------------------------------------+
| 1 | 1 | editGroup | 2009-08-19 13:21:12 | 权限组ID『2』修改成功 |
| 2 | 1 | editGroup | 2009-08-19 13:32:15 | 权限组ID『2』修改成功 |
| 3 | 1 | editGroup | 2009-08-19 16:24:33 | 权限组ID『2』修改成功 |
| 4 | 365 | login | 2009-08-20 10:31:15 | 用户『古龙12』从『10.1.69.26』登录成功 |
| 5 | 384 | login | 2009-08-20 10:31:15 | 用户『古龙31』从『10.1.69.26』登录成功 |
| 6 | 398 | logout | 2009-08-20 10:31:15 | 用户『古龙45』退出系统 |
| 7 | 397 | login | 2009-08-20 10:31:16 | 用户『古龙44』从『10.1.69.26』登录成功 |
| 8 | 419 | login | 2009-08-20 10:31:16 | 用户『古龙66』从『10.1.69.26』登录成功 |
| 9 | 365 | logout | 2009-08-20 10:31:16 | 用户『古龙12』退出系统 |
| 10 | 382 | login | 2009-08-20 10:31:16 | 用户『古龙29』从『10.1.69.26』登录成功 |
| 11 | 373 | logout | 2009-08-20 10:31:17 | 用户『古龙20』退出系统 |
| 12 | 1 | editGroup | 2009-08-19 13:21:12 | 权限组ID『2』修改成功 |
| 13 | 1 | editGroup | 2009-08-19 13:32:15 | 权限组ID『2』修改成功 |
| 14 | 1 | editGroup | 2009-08-19 16:24:33 | 权限组ID『2』修改成功 |
| 15 | 365 | login | 2009-08-20 10:31:15 | 用户『古龙12』从『10.1.69.26』登录成功 |
| 16 | 384 | login | 2009-08-20 10:31:15 | 用户『古龙31』从『10.1.69.26』登录成功 |
| 17 | 398 | logout | 2009-08-20 10:31:15 | 用户『古龙45』退出系统 |
| 18 | 397 | login | 2009-08-20 10:31:16 | 用户『古龙44』从『10.1.69.26』登录成功 |
| 19 | 419 | login | 2009-08-20 10:31:16 | 用户『古龙66』从『10.1.69.26』登录成功 |
| 20 | 365 | logout | 2009-08-20 10:31:16 | 用户『古龙12』退出系统 |
| 21 | 382 | login | 2009-08-20 10:31:16 | 用户『古龙29』从『10.1.69.26』登录成功 |
| 22 | 373 | logout | 2009-08-20 10:31:17 | 用户『古龙20』退出系统 |
| 23 | 419 | logout | 2009-08-20 10:31:17 | 用户『古龙66』退出系统 |
| 24 | 421 | login | 2009-08-20 10:31:17 | 用户『古龙68』从『10.1.69.26』登录成功 |
| 25 | 420 | login | 2009-08-20 10:31:17 | 用户『古龙67』从『10.1.69.26』登录成功 |
| 26 | 382 | logout | 2009-08-20 10:31:17 | 用户『古龙29』退出系统 |
| 27 | 397 | logout | 2009-08-20 10:31:18 | 用户『古龙44』退出系统 |
| 28 | 371 | login | 2009-08-20 10:31:18 | 用户『古龙18』从『10.1.69.26』登录成功 |
| 29 | 438 | login | 2009-08-20 10:31:18 | 用户『古龙85』从『10.1.69.26』登录成功 |
| 30 | 421 | logout | 2009-08-20 10:31:19 | 用户『古龙68』退出系统 |
| 31 | 409 | login | 2009-08-20 10:31:19 | 用户『古龙56』从『10.1.69.26』登录成功 |
| 32 | 420 | logout | 2009-08-20 10:31:20 | 用户『古龙67』退出系统 |
| 33 | 371 | logout | 2009-08-20 10:31:20 | 用户『古龙18』退出系统 |
| 34 | 375 | login | 2009-08-20 10:31:20 | 用户『古龙22』从『10.1.69.26』登录成功 |
| 35 | 438 | logout | 2009-08-20 10:31:20 | 用户『古龙85』退出系统 |
| 36 | 409 | logout | 2009-08-20 10:31:20 | 用户『古龙56』退出系统 |
| 37 | 448 | logout | 2009-08-20 10:31:20 | 用户『古龙95』退出系统 |
| 38 | 424 | login | 2009-08-20 10:31:20 | 用户『古龙71』从『10.1.69.26』登录成功 |
| 39 | 443 | logout | 2009-08-20 10:31:20 | 用户『古龙90』退出系统 |
| 40 | 438 | logout | 2009-08-20 10:31:21 | 用户『古龙85』退出系统 |
| 41 | 424 | logout | 2009-08-20 10:31:21 | 用户『古龙71』退出系统 |
| 42 | 375 | logout | 2009-08-20 10:31:21 | 用户『古龙22』退出系统 |
| 43 | 452 | login | 2009-08-20 10:31:22 | 用户『古龙99』从『10.1.69.26』登录成功 |
| 44 | 417 | login | 2009-08-20 10:31:22 | 用户『古龙64』从『10.1.69.26』登录成功 |
| 45 | 447 | login | 2009-08-20 10:31:23 | 用户『古龙94』从『10.1.69.26』登录成功 |
| 46 | 394 | login | 2009-08-20 10:31:24 | 用户『古龙41』从『10.1.69.26』登录成功 |
| 47 | 452 | logout | 2009-08-20 10:31:24 | 用户『古龙99』退出系统 |
| 48 | 444 | login | 2009-08-20 10:31:24 | 用户『古龙91』从『10.1.69.26』登录成功 |
| 49 | 447 | logout | 2009-08-20 10:31:24 | 用户『古龙94』退出系统 |
| 50 | 358 | login | 2009-08-20 10:31:24 | 用户『古龙5』从『10.1.69.26』登录成功 |
| 51 | 395 | login | 2009-08-20 10:31:24 | 用户『古龙42』从『10.1.69.26』登录成功 |
| 52 | 359 | logout | 2009-08-20 10:31:24 | 用户『古龙6』退出系统 |
| 53 | 423 | logout | 2009-08-20 10:31:24 | 用户『古龙70』退出系统 |
| 54 | 438 | logout | 2009-08-20 10:31:24 | 用户『古龙85』退出系统 |
| 55 | 417 | logout | 2009-08-20 10:31:25 | 用户『古龙64』退出系统 |
| 56 | 394 | logout | 2009-08-20 10:31:25 | 用户『古龙41』退出系统 |
| 57 | 379 | login | 2009-08-20 10:31:25 | 用户『古龙26』从『10.1.69.26』登录成功 |
| 58 | 434 | login | 2009-08-20 10:31:25 | 用户『古龙81』从『10.1.69.26』登录成功 |
| 59 | 395 | logout | 2009-08-20 10:31:25 | 用户『古龙42』退出系统 |
| 60 | 438 | login | 2009-08-20 10:31:25 | 用户『古龙85』从『10.1.69.26』登录成功 |
| 61 | 358 | logout | 2009-08-20 10:31:25 | 用户『古龙5』退出系统 |
| 62 | 444 | logout | 2009-08-20 10:31:25 | 用户『古龙91』退出系统 |
| 63 | 428 | login | 2009-08-20 10:31:25 | 用户『古龙75』从『10.1.69.26』登录成功 |
| 64 | 360 | login | 2009-08-20 10:31:25 | 用户『古龙7』从『10.1.69.26』登录成功 |
| 65 | 386 | login | 2009-08-20 10:31:26 | 用户『古龙33』从『10.1.69.26』登录成功 |
| 66 | 379 | logout | 2009-08-20 10:31:26 | 用户『古龙26』退出系统 |
| 67 | 434 | logout | 2009-08-20 10:31:26 | 用户『古龙81』退出系统 |
| 68 | 423 | login | 2009-08-20 10:31:26 | 用户『古龙70』从『10.1.69.26』登录成功 |
| 69 | 360 | logout | 2009-08-20 10:31:26 | 用户『古龙7』退出系统 |
| 70 | 384 | login | 2009-08-20 10:31:26 | 用户『古龙31』从『10.1.69.26』登录成功 |
| 71 | 428 | login | 2009-08-20 10:31:26 | 用户『古龙75』从『10.1.69.26』登录成功 |
| 72 | 428 | logout | 2009-08-20 10:31:26 | 用户『古龙75』退出系统 |
+----+---------+-------------+---------------------+----------------------------------------+
72 rows in set (0.00 sec)mysql> select * from sys_user_journal j
-> where BUSINESS_ID='login'
-> and not exists (select 1
-> from sys_user_journal
-> where USER_ID=j.USER_ID
-> and BUSINESS_ID='login'
-> and CREATE_TIME>j.CREATE_TIME);
+----+---------+-------------+---------------------+----------------------------------------+
| ID | USER_ID | BUSINESS_ID | CREATE_TIME | DESCRIPTION |
+----+---------+-------------+---------------------+----------------------------------------+
| 4 | 365 | login | 2009-08-20 10:31:15 | 用户『古龙12』从『10.1.69.26』登录成功 |
| 7 | 397 | login | 2009-08-20 10:31:16 | 用户『古龙44』从『10.1.69.26』登录成功 |
| 8 | 419 | login | 2009-08-20 10:31:16 | 用户『古龙66』从『10.1.69.26』登录成功 |
| 10 | 382 | login | 2009-08-20 10:31:16 | 用户『古龙29』从『10.1.69.26』登录成功 |
| 15 | 365 | login | 2009-08-20 10:31:15 | 用户『古龙12』从『10.1.69.26』登录成功 |
| 18 | 397 | login | 2009-08-20 10:31:16 | 用户『古龙44』从『10.1.69.26』登录成功 |
| 19 | 419 | login | 2009-08-20 10:31:16 | 用户『古龙66』从『10.1.69.26』登录成功 |
| 21 | 382 | login | 2009-08-20 10:31:16 | 用户『古龙29』从『10.1.69.26』登录成功 |
| 24 | 421 | login | 2009-08-20 10:31:17 | 用户『古龙68』从『10.1.69.26』登录成功 |
| 25 | 420 | login | 2009-08-20 10:31:17 | 用户『古龙67』从『10.1.69.26』登录成功 |
| 28 | 371 | login | 2009-08-20 10:31:18 | 用户『古龙18』从『10.1.69.26』登录成功 |
| 31 | 409 | login | 2009-08-20 10:31:19 | 用户『古龙56』从『10.1.69.26』登录成功 |
| 34 | 375 | login | 2009-08-20 10:31:20 | 用户『古龙22』从『10.1.69.26』登录成功 |
| 38 | 424 | login | 2009-08-20 10:31:20 | 用户『古龙71』从『10.1.69.26』登录成功 |
| 43 | 452 | login | 2009-08-20 10:31:22 | 用户『古龙99』从『10.1.69.26』登录成功 |
| 44 | 417 | login | 2009-08-20 10:31:22 | 用户『古龙64』从『10.1.69.26』登录成功 |
| 45 | 447 | login | 2009-08-20 10:31:23 | 用户『古龙94』从『10.1.69.26』登录成功 |
| 46 | 394 | login | 2009-08-20 10:31:24 | 用户『古龙41』从『10.1.69.26』登录成功 |
| 48 | 444 | login | 2009-08-20 10:31:24 | 用户『古龙91』从『10.1.69.26』登录成功 |
| 50 | 358 | login | 2009-08-20 10:31:24 | 用户『古龙5』从『10.1.69.26』登录成功 |
| 51 | 395 | login | 2009-08-20 10:31:24 | 用户『古龙42』从『10.1.69.26』登录成功 |
| 57 | 379 | login | 2009-08-20 10:31:25 | 用户『古龙26』从『10.1.69.26』登录成功 |
| 58 | 434 | login | 2009-08-20 10:31:25 | 用户『古龙81』从『10.1.69.26』登录成功 |
| 60 | 438 | login | 2009-08-20 10:31:25 | 用户『古龙85』从『10.1.69.26』登录成功 |
| 64 | 360 | login | 2009-08-20 10:31:25 | 用户『古龙7』从『10.1.69.26』登录成功 |
| 65 | 386 | login | 2009-08-20 10:31:26 | 用户『古龙33』从『10.1.69.26』登录成功 |
| 68 | 423 | login | 2009-08-20 10:31:26 | 用户『古龙70』从『10.1.69.26』登录成功 |
| 70 | 384 | login | 2009-08-20 10:31:26 | 用户『古龙31』从『10.1.69.26』登录成功 |
| 71 | 428 | login | 2009-08-20 10:31:26 | 用户『古龙75』从『10.1.69.26』登录成功 |
+----+---------+-------------+---------------------+----------------------------------------+
29 rows in set (0.06 sec)mysql>