编写筛选时间的存储过程
有两张表test和location分别代表测试信息和位置信息,现需要建立存储过程,筛选出测试信息表中所有位置不在石家庄的信息(也就是说位置在石家庄到位置变为别的地点的这段时间的测试信息过滤掉),会的请把代码写一下。
建立测试信息表:
[code=sql]create table test
(
id1 int(10) not null auto_increment,
name varchar(20),
resule varchar(10),
time datatime()
)
insert into test values('1','A','10','2018-06-17 06:22:00');
insert into test values('2','A','5','2018-06-17 06:23:02');
insert into test values('3','A','4','2018-06-17 06:24:00');
insert into test values('4','A','11','2018-06-17 06:25:00');
insert into test values('5','A','9','2018-06-17 06:26:00');
insert into test values('6','A','5','2018-06-17 06:30:00');
insert into test values('7','B','19','2018-06-17 05:22:00');
insert into test values('8','B','6','2018-06-17 05:23:00');
insert into test values('9','A','6','2018-06-17 06:20:00');
insert into test values('10','A','6','2018-06-17 06:19:00');
insert into test values('11','B','17','2018-06-17 05:25:00');
insert into test values('12','B','14','2018-06-17 05:24:00');
insert into test values('13','B','15','2018-06-17 05:26:00');
insert into test values('14','B','15','2018-06-17 05:30:00');
insert into test values('15','B','13','2018-06-17 05:31:00');
insert into test values('16','B','12','2018-06-17 05:28:00');
insert into test values('17','B','11','2018-06-17 05:27:00');
insert into test values('18','B','10','2018-06-17 05:28:04');
insert into test values('19','A','7','2018-06-17 06:21:00');
insert into test values('20','A','6','2018-06-17 06:28:00');
建立位置表
create table location
(
id2 int(10) not null auto_increment,
name varchar(20),
loc varchar(20),
time datetime()
)
insert into location values('1','A','石家庄','2018-06-17 06:23:00');
insert into location values('2','A','银川','2018-06-17 06:25:02');
insert into location values('3','A','石家庄','2018-06-17 06:29:00');
insert into location values('4','B','海口','2018-06-17' 05:13:00);
insert into location values('5','B','石家庄','2018-06-17' 05:23:04);
insert into location values('6','B','鞍山','2018-06-17' 05:26:02);
结果为:id1 name result time
5 A 9 2018-6-17 06:26:00
20 A 6 2018-6-17 06:28:00
7 B 19 2018-6-17 05:22:00
8 B 20 2018-6-17 05:23:00
14 B 15 2018-6-17 05:30:00
15 B 13 2018-6-17 05:31:00
16 B 12 2018-6-17 05:28:00
17 B 19 2018-6-17 05:27:00
18 B 10 2018-6-17 05:28:04