CREATE TABLE IF NOT EXISTS `mj_user_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` char(20) DEFAULT NULL,
`log_type` varchar(20) DEFAULT NULL,
`log_content` varchar(1024) DEFAULT NULL,
`create_datetime` char(14) DEFAULT NULL,
`online_count` int(10) unsigned DEFAULT NULL,
`ip` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_log_userid` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
表如上
我想查出条件为按user_id查找并且相同user_id最新的两条记录的create_datetime差值为10的user_id
这条SQL语句该如何写。。请教
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` char(20) DEFAULT NULL,
`log_type` varchar(20) DEFAULT NULL,
`log_content` varchar(1024) DEFAULT NULL,
`create_datetime` char(14) DEFAULT NULL,
`online_count` int(10) unsigned DEFAULT NULL,
`ip` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_log_userid` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
表如上
我想查出条件为按user_id查找并且相同user_id最新的两条记录的create_datetime差值为10的user_id
这条SQL语句该如何写。。请教
(1, 'U2011122700000000001', 'logon', 'admin logon ok,ip:110.80.44.44,logon numb 144', '20120227205109', 1, ''),
(2, 'U2011122700000000001', 'logout', 'admin logout ok,ip:110.80.44.44', '20120227205616', 0, ''),
(3, 'U2011122700000000001', 'logon', 'admin logon ok,ip:110.80.44.44,logon numb 145', '20120227205738', 1, ''),
(4, 'U2011122700000000001', 'logout', 'admin logout ok,ip:110.80.44.44', '20120227211022', 0, ''),
(5, 'U2012022700000001501', 'logon', '6969 logon ok,ip:110.80.44.44,logon numb 1', '20120227211555', 1, ''),
(6, 'U2012022700000001501', 'logout', '6969 logout ok,ip:110.80.44.44', '20120227211756', 0, ''),
(7, 'U2012022700000001509', 'logon', 'azazely85 logon ok,ip:82.207.94.131,logon numb 1', '20120227214830', 1, ''),
(8, 'U2012022700000001420', 'logon', 'drakkainen logon ok,ip:91.145.137.132,logon numb 1', '20120227215330', 2, '');
一些记录
我想查询出表中所有的最新的两条记录的create_datetime相减等于10000的user_id
form
(
select
from mj_user_log a
where 1=(select count(*) from mj_user_log where user_id=a.user_id and create_datetime>a.create_datetime)
) u,
(
select
from mj_user_log b
where 0=(select count(*) from mj_user_log where user_id=a.user_id and create_datetime>a.create_datetime)
) v
where u.user_id=v.user_id
and u.create_datetime>v.create_datetime+10000