现有数据库某表内容如下:
id role_id login_time is_login
1403 100010421 2012/10/19 09:25:24 1
1439 100010421 2012/10/19 11:03:28 0
1440 100010421 2012/10/19 11:11:45 1
1441 100010421 2012/10/19 11:14:59 0
1470 100010421 2012/10/19 13:27:00 0
1474 100010421 2012/10/19 13:42:02 1
1475 100010421 2012/10/19 13:42:46 0怎么获取到如下的查询结果:(即不能有连续两条登陆或登出的记录)
id role_id login_time is_login
1403 100010421 2012/10/19 09:25:24 1
1439 100010421 2012/10/19 11:03:28 0
1440 100010421 2012/10/19 11:11:45 1
1470 100010421 2012/10/19 13:27:00 0
1474 100010421 2012/10/19 13:42:02 1
1475 100010421 2012/10/19 13:42:46 0
id role_id login_time is_login
1403 100010421 2012/10/19 09:25:24 1
1439 100010421 2012/10/19 11:03:28 0
1440 100010421 2012/10/19 11:11:45 1
1441 100010421 2012/10/19 11:14:59 0
1470 100010421 2012/10/19 13:27:00 0
1474 100010421 2012/10/19 13:42:02 1
1475 100010421 2012/10/19 13:42:46 0怎么获取到如下的查询结果:(即不能有连续两条登陆或登出的记录)
id role_id login_time is_login
1403 100010421 2012/10/19 09:25:24 1
1439 100010421 2012/10/19 11:03:28 0
1440 100010421 2012/10/19 11:11:45 1
1470 100010421 2012/10/19 13:27:00 0
1474 100010421 2012/10/19 13:42:02 1
1475 100010421 2012/10/19 13:42:46 0
from tb A
where not exists (select 1 from tb B where B.role_id=A.role_id and B.id=(select min(id) from tb C where A.role_id = C.role_id and C.login_time>A.login_time) and B.id=A.id )
CREATE TABLE `t_log_login` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`ROLE_ID` int(11) NOT NULL,
`LOGIN_TIME` datetime NOT NULL,
`IS_LOGIN_IN` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=206 DEFAULT CHARSET=gbk ;insert into t_log_login values(1403,100010421,'2012-10-19 09:25:24',1);
insert into t_log_login values(1439,100010421,'2012-10-19 11:03:28',0);
insert into t_log_login values(1440,100010421,'2012-10-19 11:11:45',1);
insert into t_log_login values(1441,100010421,'2012-10-19 11:14:59',0);
insert into t_log_login values(1470,100010421,'2012-10-19 13:27:00',0);
insert into t_log_login values(1474,100010421,'2012-10-19 13:42:02',1);
insert into t_log_login values(1475,100010421,'2012-10-19 13:42:46',0);
CREATE TABLE `t_log_logina`
sELECT @num:=@num+1 AS js ,* FROM `t_log_login`;SELECT * FROM `t_log_logina` a WHERE NOT EXISTS(
SELECT 1 FROM `t_log_logina` WHERE a.`ROLE_ID`=`ROLE_ID`
AND a.`IS_LOGIN_IN`=`IS_LOGIN_IN` AND a.`js`=`js`-1);
Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE `t_log_logina`
-> SELECT *,@num:=@num+1 AS js FROM `t_log_login`;
Query OK, 7 rows affected (0.08 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql>
mysql> SELECT * FROM `t_log_logina` a WHERE NOT EXISTS(
-> SELECT 1 FROM `t_log_logina` WHERE a.`ROLE_ID`=`ROLE_ID`
-> AND a.`IS_LOGIN_IN`=`IS_LOGIN_IN` AND a.`js`=`js`-1
->
-> );
+------+-----------+---------------------+-------------+------+
| ID | ROLE_ID | LOGIN_TIME | IS_LOGIN_IN | js |
+------+-----------+---------------------+-------------+------+
| 1403 | 100010421 | 2012-10-19 09:25:24 | 1 | 1 |
| 1439 | 100010421 | 2012-10-19 11:03:28 | 0 | 2 |
| 1440 | 100010421 | 2012-10-19 11:11:45 | 1 | 3 |
| 1470 | 100010421 | 2012-10-19 13:27:00 | 0 | 5 |
| 1474 | 100010421 | 2012-10-19 13:42:02 | 1 | 6 |
| 1475 | 100010421 | 2012-10-19 13:42:46 | 0 | 7 |
+------+-----------+---------------------+-------------+------+
6 rows in set (0.00 sec)mysql>