mysql - 根据另一个表的最新日期更新 2 个表列

Question

我搜索了这个网站并尝试了相当多的 sql 变体，但似乎无法获得正确的结果。

我有两个表，一个名为“events”，另一个名为“outstanding”。我已在未完成的表“Last_event”和“Event_date”中添加了两列。每个表的唯一标识符是“Account_number”。我试图在“事件”表中查找 Account_number 中的所有事件，其中“Event_name”字段包含“Collections”，然后找到最新的相关“Created_date”。该日期将保存在“未完成”表的“Event_date”字段中，相关的“Event_name”将保存在“Last_event”字段中。

这是我所拥有的:

UPDATE outstanding as C 
inner join (SELECT l.Account_number, e.Created_date, e.Event_name 
            FROM outstanding l 
            INNER JOIN events e on(l.Account_number = e.Account_number) 
            WHERE Event_name LIKE 'Collections:%'
            ORDER BY `e`.`Created_date` desc) AS D on(C.Account_number = D.Account_number) 
set C.Event_date = D.`Created_date`, 
    C.Last_event = D.`Event_name`

这非常接近工作，但是，如果有多个事件，它会按字母顺序保存第一个“Event_name”。

如果需要更多信息，请告诉我。任何帮助将不胜感激。

这里是示例数据:

事件表:

优秀表:

预期结果:

以下是表结构:

事件表:

优秀表:

Answer 1

我认为您的INNER JOIN比您需要的更复杂。您真正想要的只是与名称中包含“Collections” 一词的帐户上的事件关联的最近(最大)日期。请注意，由于您的日期不是 MySQL 格式，因此您需要先对其进行转换，然后才能进行测试。您可以使用以下查询来做到这一点:

UPDATE outstanding as C 
inner join (SELECT Account_number, Created_date, Event_name 
            FROM events e
            WHERE Event_name LIKE '%Collections%' AND
               STR_TO_DATE(Created_date, '%m/%d/%y %H:%i') = (SELECT MAX(STR_TO_DATE(Created_date, '%m/%d/%y %H:%i')) 
                               FROM events e1
                               WHERE e1.Account_Number = e.Account_Number AND
                                     e1.Event_name LIKE '%Collections%')
            ) AS D on (C.Account_number = D.Account_number) 
set C.Event_date = D.Created_date, 
    C.Last_event = D.`Event_name`

更新后的表格内容(基于您的示例数据):

id  Account_number  Last_event                                  Event_date
19  8599            Collections: Sent to Outside Collections    2/16/18 0:00

Demo on dbfiddle

更新

根据提供的 fiddle ，表中的日期实际上采用 dd/mm/YYYY HH:mm 格式，而不是 dd/mm/yy HH:mm如问题中的样本数据所示。因此对 STR_TO_DATE 的调用需要调整:

UPDATE outstanding as C 
inner join (SELECT Account_number, Created_date, Event_name 
            FROM events e
            WHERE Event_name LIKE '%Collections%' AND
               STR_TO_DATE(Created_date, '%m/%d/%Y %H:%i') = (SELECT MAX(STR_TO_DATE(Created_date, '%m/%d/%Y %H:%i')) 
                               FROM events e1
                               WHERE e1.Account_Number = e.Account_Number AND
                                     e1.Event_name LIKE '%Collections%')
            ) AS D on (C.Account_number = D.Account_number) 
set C.Event_date = D.Created_date, 
    C.Last_event = D.`Event_name`

Updated demo