create table tb(time_id datetime,agent varchar(10),verb varchar(10))
insert into tb values('2009-01-14 18:00:00','李', '登陆');
insert into tb values('2009-01-14 22:00:00','李', '退出');
insert into tb values('2009-01-15 12:00:00','赵', '登陆');
insert into tb values('2009-01-15 13:00:00','李', '登陆');
insert into tb values('2009-01-15 14:00:00','赵', '退出');----用于统计的算起来语句
select a.agent , sum(datediff(mi,a.time_id,b.time_id)) from (select * , (select count(*) from tb where agent = t.agent and verb = '登陆' and time_id between '2009-01-14 12:00:00 ' and '2009-01-15 18:00:00' and time_id < t.time_id) + 1 px from tb t) a ,(select * , (select count(*) from tb where agent = t.agent and verb = '退出' and time_id between '2009-01-14 12:00:00 ' and '2009-01-15 18:00:00' and time_id < t.time_id) + 1 px from tb t) b where a.agent =b.agent and a.px = b.px group by a.agent;
----所报的错误
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b.time_id)) from (select * , (select count(*) from tb where agent = t.agent a' at line 1
create table tb(时间 datetime,用户名 varchar(10),动作 varchar(10))
insert into tb values('2009-01-15 18:00:00','李', '登陆')
insert into tb values('2009-01-15 22:00:00','李', '退出')
insert into tb values('2009-01-14 12:00:00','赵', '登陆')
insert into tb values('2009-01-14 13:00:00','李', '登陆')
insert into tb values('2009-01-14 12:00:00','赵', '退出')
go
select m.用户名 , sum(datediff(mi,m.时间,n.时间)) 分钟 from
(select * , (select count(*) from tb where 用户名 = t.用户名 and 动作 = '登陆' and 时间 between '2009-01-14 12:00:00 ' and '2009-01-15 18:00:00' and 时间 < t.时间) + 1 px from tb t) m ,
(select * , (select count(*) from tb where 用户名 = t.用户名 and 动作 = '退出' and 时间 between '2009-01-14 12:00:00 ' and '2009-01-15 18:00:00' and 时间 < t.时间) + 1 px from tb t) n
where m.用户名 = n.用户名 and m.px = n.px
group by m.用户名drop table tb/*
用户名 分钟
---------- -----------
李 3720
赵 0（所影响的行数为 2 行）
*/