我有一张葡萄补丁表。每个贴片都有一个喷雾表,其中有日期和喷雾类型,每个喷雾表都有一个“最短时间”,直到您可以选择为止。
补丁表:
PatchID | earliestDate
1 ---
2 ---
喷雾台
SprayID | PatchID | TypeID |Date
1 1 1 2019-06-1
2 1 2 2019-06-16
1 2 1 2019-06-16
2 2 2 2019-06-1
喷涂类型
TypeID | minimumTime
1 14
2 28
我抓到了
MAX(newEarliest) FROM ( SELECT DATE_ADD(Spray.Date,INTERVAL Type.minimumTime DAY) As newEarliest
FROM Spray
LEFT JOIN Patch ON Patch.PatchID = Spray.PatchID
LEFT JOIN Type ON Type.TypeID = Spray.TypeID) WHERE Patch.PatchID = 1;
但是从这里我被困住了
UPDATE Patch SET EarliestDate = MAX(newEarliest) FROM ( SELECT DATE_ADD(Spray.Date,INTERVAL Type.minimumTime DAY) As newEarliest
FROM Spray
LEFT JOIN Patch ON Patch.PatchID = Spray.PatchID
LEFT JOIN Type ON Type.TypeID = Spray.TypeID) ) WHERE ??;
预期的结果应该是,补丁1到达7月14日,补丁2到达7月30日。.但是我无法建立最后一个连接。我希望/期待吗?我刚刚撞墙,需要喘口气,但也许我错过了一条SQL命令?