有两张表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
建立测试信息表:
[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
解决方案 »
- 请问大家在工作中都用的是什么mysql客户端工具?
- sql问题
- mysql root 权限消失
- MySql——Front是否有设置主键的功能
- MySQL史上最难的难题,急求高人!高人请进!LEFT JOIN + UNION 四个表
- 求,MYSQL级联删除
- 怎样写一个每天定时执行的一个脚本,用来定时备份数据(mysql,在linux下)
- 怎样在刚装好的mysql上运行命令,知道我的系统安装成功(redhat7.2)
- 在linux下 找不到mysql.sock怎么办?
- MySQL支持视图索引吗?
- SQL技术内幕:InnoDB存储引擎 高性能MySQL 这两本书推荐哪个??
- 商品订单和物流表的ER图怎么画?
insert into location values('2','A','银川','2018-06-17 06:25:02'); 在06:23:00到6:25:02这段时间是在石家庄的,这段时间的测试信息要删除,而id1为5的时间显示为6:26:00,不应被删除