MySQL - 存储过程或简单联接，根据过滤器和最大值将一个表中的值获取到另一个表中

Question

我想知道在 mysql 中执行以下操作的更好方法是什么:

如果我有一个具有以下结构和虚拟数据的表 A:

+------------+-----+
|   Dates    | ID  |
+------------+-----+
| 01/01/2017 | 123 |
| 02/01/2017 | 123 |
| 03/01/2017 | 123 |
| 04/01/2017 | 123 |
| 06/01/2017 | 123 |
| 07/01/2017 | 123 |
| 01/01/2017 | 124 |
| 02/01/2017 | 124 |
| 03/01/2017 | 124 |
| 04/01/2017 | 124 |
| 06/01/2017 | 124 |
| 07/01/2017 | 124 |
+------------+-----+

和一个包含一些信息的表 B，我想从中获取属性数据:

+------------+-----+-----------+
|   Dates    | ID  | Attribute |
+------------+-----+-----------+
| 29/12/2016 | 123 | AA        |
| 30/12/2016 | 123 | AB        |
| 31/12/2016 | 123 | AC        |
| 01/01/2017 | 123 | AD        |
| 03/01/2017 | 123 | AF        |
| 04/01/2017 | 123 | AA        |
| 07/01/2017 | 123 | AF        |
| 10/01/2017 | 123 | AC        |
| 27/12/2016 | 124 | BA        |
| 28/12/2016 | 124 | BB        |
| 29/12/2016 | 124 | BC        |
| 30/12/2016 | 124 | BD        |
| 31/12/2016 | 124 | BE        |
| 02/01/2017 | 124 | BF        |
| 04/01/2017 | 124 | BA        |
| 06/01/2017 | 124 | AA        |
| 07/01/2017 | 124 | AC        |
| 11/01/2017 | 124 | BF        |
+------------+-----+-----------+

根据以下条件，如何连接这两个表以获得属性信息:

1. 在表 A 的许多记录中，表 B 中不会有匹配的日期，因此无法对 ID 和日期进行直接且简单的联接；

2. 当表 B 中没有匹配的日期 d 时，对于表 A 中的给定 ID，我需要从前一个日期获取属性(前一个日期可以是前一天或日期 d) 之前几天。

我想要获得的结果表是这样的，如下表:

+----------------------+---------------------+----------------------------+
| Dates (from table A) | ID ( from table A ) | Attribute ( from table B ) |
+----------------------+---------------------+----------------------------+
| 01/01/2017           |                 123 | AD                         |
| 02/01/2017           |                 123 | AD                         |
| 03/01/2017           |                 123 | AF                         |
| 04/01/2017           |                 123 | AA                         |
| 06/01/2017           |                 123 | AA                         |
| 07/01/2017           |                 123 | AF                         |
| 01/01/2017           |                 124 | BE                         |
| 02/01/2017           |                 124 | BF                         |
| 03/01/2017           |                 124 | BF                         |
| 04/01/2017           |                 124 | BA                         |
| 06/01/2017           |                 124 | AA                         |
| 07/01/2017           |                 124 | AC                         |
+----------------------+---------------------+----------------------------+

请注意，举个例子:对于 2017 年 2 月 1 日的 ID = 123，表 B 中没有任何记录，因此我需要获取前一天的属性，在本例中是 2017 年 1 月 1 日的属性 - 属性= AD。

正如您所看到的，这是类似于我的真实数据的虚拟数据，而我的真实数据在每个表中有数百个注册表。因此，我也在寻找一种能够很好地执行我的数据库的解决方案。我想也许我们需要一个存储过程来循环表 A 中的每一行并获得我期望的结果，这是一个好主意吗？同时，我想知道这是否可以通过连接操作来完成......我现在真的很困惑:)

我希望这个解释足够清楚，如果以前已经回答过类似的问题，我真的很抱歉。然而，我已经研究了几天了，并且做了很多搜索，但没有找到类似问题的答案。我提前感谢您的帮助。干杯。

编辑:我更改了Therms注册表/记录的注册表。

Answer 1

例如，实现此目的的一种方法是使用相关子查询

drop table if exists tb;
drop table if exists ta;
create table ta(Dates date,    ID  int);
insert into ta values
(str_to_date('01/01/2017','%d/%m/%Y'),123), 
(str_to_date('02/01/2017','%d/%m/%Y'),123), 
(str_to_date('03/01/2017','%d/%m/%Y'),123), 
(str_to_date('04/01/2017','%d/%m/%Y'),123), 
(str_to_date('06/01/2017','%d/%m/%Y'),123), 
(str_to_date('07/01/2017','%d/%m/%Y'),123), 
(str_to_date('01/01/2017','%d/%m/%Y'),124), 
(str_to_date('02/01/2017','%d/%m/%Y'),124), 
(str_to_date('03/01/2017','%d/%m/%Y'),124), 
(str_to_date('04/01/2017','%d/%m/%Y'),124), 
(str_to_date('06/01/2017','%d/%m/%Y'),124), 
(str_to_date('07/01/2017','%d/%m/%Y'),124);

create table tb(   Dates  date,   ID int ,Attribute varchar(2));
insert into tb values
( str_to_date('29/12/2016','%d/%m/%Y'),123 , 'AA'),        
( str_to_date('30/12/2016','%d/%m/%Y'),123 , 'AB'),        
( str_to_date('31/12/2016','%d/%m/%Y'),123 , 'AC'),        
( str_to_date('01/01/2017','%d/%m/%Y'),123 , 'AD'),        
( str_to_date('03/01/2017','%d/%m/%Y'),123 , 'AF'),        
( str_to_date('04/01/2017','%d/%m/%Y'),123 , 'AA'),        
( str_to_date('07/01/2017','%d/%m/%Y'),123 , 'AF'),        
( str_to_date('10/01/2017','%d/%m/%Y'),123 , 'AC'),        
( str_to_date('27/12/2016','%d/%m/%Y'),124 , 'BA'),        
( str_to_date('28/12/2016','%d/%m/%Y'),124 , 'BB'),        
( str_to_date('29/12/2016','%d/%m/%Y'),124 , 'BC'),        
( str_to_date('30/12/2016','%d/%m/%Y'),124 , 'BD'),        
( str_to_date('31/12/2016','%d/%m/%Y'),124 , 'BE'),        
( str_to_date('02/01/2017','%d/%m/%Y'),124 , 'BF'),        
( str_to_date('04/01/2017','%d/%m/%Y'),124 , 'BA'),        
( str_to_date('06/01/2017','%d/%m/%Y'),124 , 'AA'),        
( str_to_date('07/01/2017','%d/%m/%Y'),124 , 'AC'),        
( str_to_date('11/01/2017','%d/%m/%Y'),124 , 'BF');


select ta.dates, ta.id,(select attribute from tb where tb.id = ta.id and tb.dates <= ta.dates order by tb.dates desc limit 1) attribute
from ta;

+------------+------+-----------+
| dates      | id   | attribute |
+------------+------+-----------+
| 2017-01-01 |  123 | AD        |
| 2017-01-02 |  123 | AD        |
| 2017-01-03 |  123 | AF        |
| 2017-01-04 |  123 | AA        |
| 2017-01-06 |  123 | AA        |
| 2017-01-07 |  123 | AF        |
| 2017-01-01 |  124 | BE        |
| 2017-01-02 |  124 | BF        |
| 2017-01-03 |  124 | BF        |
| 2017-01-04 |  124 | BA        |
| 2017-01-06 |  124 | AA        |
| 2017-01-07 |  124 | AC        |
+------------+------+-----------+
12 rows in set (0.01 sec)