续前段时间的问题,加深一步。
create table areaemployee
(man varchar(30),
area varchar(30),
gottime varchar(30),
context varchar(30)
)insert into areaemployee(man,area,gottime,context) values ('张三','A','08:00','1');
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:02','1');
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:04','1');
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:05','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','08:01','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','08:03','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','08:06','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','09:00','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:01','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:02','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:04','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:10','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:09','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:11','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:13','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:20','1');想获得的结果:
man area min(gottime) max(gottime)
张三 A 08:00 08:05
张三 B 09:01 09:10
李四 A 08:01 09:00
李四 B 09:09 09:20也就是要获取man具体某段时间在某一地点,初始时间和结束时间。请高手指点!
create table areaemployee
(man varchar(30),
area varchar(30),
gottime varchar(30),
context varchar(30)
)insert into areaemployee(man,area,gottime,context) values ('张三','A','08:00','1');
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:02','1');
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:04','1');
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:05','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','08:01','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','08:03','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','08:06','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','09:00','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:01','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:02','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:04','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:10','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:09','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:11','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:13','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:20','1');想获得的结果:
man area min(gottime) max(gottime)
张三 A 08:00 08:05
张三 B 09:01 09:10
李四 A 08:01 09:00
李四 B 09:09 09:20也就是要获取man具体某段时间在某一地点,初始时间和结束时间。请高手指点!
set @a = 0;
set @b ='';
SELECT bz,`AREA`,max(gottime),min(gottime) FROM (
SELECT *,@a:=IF(@b=`area`,@a,@a+1) AS bz,@b:=`area` FROM rfid_areaemployee) a GROUP BY bz,`AREA`;
select man,area,min(goottime),max(gottime) from areaemployee group by man,area
得到的结果是:
man area min(gottime) max(gottime)
张三 A 08:00 08:05
张三 B 09:01 09:10
李四 A 08:01 09:00
李四 B 09:09 09:20
获得这个结果放入新建的表employee_position中;因为表areaemployee中的gottime是随着时间在递增的,也就是说表areaemployee一直在读数据,时间一直改变,因此查询得到的max(gottime)其实也是一直在变化,那么我想要实现的就是 点击select * from employee_position查询,max(gottime)的值也一直变化
例如:
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:00','1');
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:02','1');
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:04','1');
insert into areaemployee(man,area,gottime,context) values ('张三','A','08:05','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','08:01','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','08:03','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','08:06','1');
insert into areaemployee(man,area,gottime,context) values ('李四','A','09:00','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:01','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:02','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:04','1');
insert into areaemployee(man,area,gottime,context) values ('张三','B','09:10','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:09','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:11','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:13','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:20','1');
insert into areaemployee(man,area,gottime,context) values ('李四','B','09:21','1');
.....省略号的意思就是表一直在自动的动态增加数据,想要实现的结果就是
一开始查询得到的:
man area min(gottime) max(gottime)
张三 A 08:00 08:05
张三 B 09:01 09:10
李四 A 08:01 09:00
李四 B 09:09 09:20
点击select * from employee_position 查询后
结果变成:
man area min(gottime) max(gottime)
张三 A 08:00 08:05
张三 B 09:01 09:10
李四 A 08:01 09:00
李四 B 09:09 09:21
最后一条数据 李四 B 09:09 09:20 变成了李四 B 09:09 09:21