Have two table test and the location represent the test information and location information, we need to build a stored procedure, screening test information in the table all the location is not in shijiazhuang information (i.e. location to location to other sites in shijiazhuang this paragraph of time the test information filtering), will write the code, please,
Set up test information table:
[code=SQL] create table test
(
Id1 int (10) not null auto_increment,
The name varchar (20),
Resule varchar (10),
Time datatime ()
)
Insert into the 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 the 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 the 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 the 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');
To establish position table
The create table location
(
Id2 int (10) not null auto_increment,
The name varchar (20),
Loc varchar (20),
Time a datetime ()
)
Insert into the location values (' 1 ', 'A', 'shijiazhuang,' the 2018-06-17 06:23:00 ');
Insert into the location values (' 2 ', 'A', 'yinchuan,' the 2018-06-17 06:25:02 ');
Insert into the location values (' 3 ', 'A', 'shijiazhuang,' the 2018-06-17 06:29:00 ');
Insert into the location values (' 4 ', 'B', 'haikou', '2018-06-17' 05:13:00);
Insert into the location values (' 5 ', 'B', 'shijiazhuang', '2018-06-17' 05:23:04);
Insert into the location values (' 6 ', 'B', 'anshan', '2018-06-17' 05:26:02);


The result is: the 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 15 2018-6-17 B 05:30:00
13 the 2018-6-17 05:31:00 15 B
16 B 12 2018-6-17 05:28:00
17 B 19 2018-6-17 05:27:00
18 10 05:28:04 2018-6-17 B

CodePudding user response:

Didn't see the building the rules of the result data

CodePudding user response:

Is the position information table and associated test information table, query operation, as long as the location information positioning displays information about the test this time in shijiazhuang are removed

CodePudding user response:

refer to the second floor auewho response:

is the location information table and the associated test information table, query operation, as long as the location information positioning displays information about the test this time in shijiazhuang are removed

Related field is the name? Why the insert into the test values (' 6 ', 'A', '5', '2018-06-17 06:30:00'); This article take out?

CodePudding user response:

Is the name for associated fields, id1 is 6 of this article in the results did not show, in the result has been removed

CodePudding user response:

reference 4 floor auewho response:

is the name for associated fields, id1 is 6 of this article in the results did not show, in the result has been to remove

Id1=5, not 6, the above said is wrong

CodePudding user response:

Location information in the table A insert into the location values (' 1 ', 'A', 'shijiazhuang,' the 2018-06-17 06:23:00 ');
Insert into the location values (' 2 ', 'A', 'yinchuan,' the 2018-06-17 06:25:02 '); In 06:23:00 6:25:02 this paragraph of time is in shijiazhuang, this paragraph of time the test information to be deleted, while id1 for 5 time display for 6:26:00, should not be deleted

CodePudding user response:

How's that？ Do you have any ideas?