mysql - 更新表字段的复杂查询。这可能吗？

Question

我有两个名为 Item 和 Inspection 的数据库表。一个项目有很多检查。基本上，检查是对一个项目进行的，检查记录它的状态是“未损坏”还是“损坏”。对每个项目进行定期检查。

我想为 Items 表中的每条记录更新一个名为“last_broken_at”的新添加字段。它需要更新为在该项目的最早相关检查中找到的“创建”日期时间，其中检查“状态”字段等于“损坏”。

虽然这绝对可以通过单个 MySQL 更新语句实现，但这里有一个警告:

如果某个项目在某个时间点被修复并且检查发现该项目“未损坏”，但后来发现再次“损坏”，则该项目的“last_broken_at”字段必须更新为“创建”日期时间检查状态“未损坏”后状态为“损坏”的检查。

基本上，Items 表中的“last_broken_at”字段必须如其名，它确实是我们知道 Item 从“未损坏”到“损坏”的最接近时间。

表格如下:

项目表

• 身份证(国际)
• 创建(DATETIME)
• 名称(VARCHAR)
• status (VARCHAR) 注意:包含项目的最新状态，根据上次检查。
• last_broken_at (DATETIME)

检查

• 身份证(国际)
• 创建(DATETIME)
• 项目状态(VARCHAR)
• item_id(整数)

谢谢!!

Answer 1

尝试:

UPDATE items t 
SET    t.last_broke_at = (
                          SELECT min(created) 
                          FROM   inspections t1 
                          WHERE  t.item_id=t1.item_id 
                             AND status='broken' 
                             AND NOT EXISTS (
                                             SELECT * 
                                             FROM   inspections t2 
                                             WHERE  t1.item_id = t2.item_id 
                                                AND t2.created> t1.created 
                                                AND t2.status = 'not broken'
                                            )
                         )