MySQL按另一个表的最小值更新一个表

Question

我想更新TableLeasefrom TableHistory

CREATE TABLE Lease
(`LeaseID` int, `Name` varchar(3), `Users` varchar(15), `WhoSignID` int, `NoteDate` date)
;

INSERT INTO Lease
(`LeaseID`, `Name`, `Users`, `WhoSignID`, `NoteDate`)
VALUES
(1, 'AAA', '1000,1001', NULL, NULL),
(2, 'BBB', '1002', NULL, NULL),
(3, 'CCC', '1003,1004', NULL, NULL),
(4, 'DDD', '1005,1006, 1007', NULL, NULL)
;
CREATE TABLE History
(`HistoryID` int, `LeaseID` int, `User` int, `SignDate` date)
;

INSERT INTO History
(`HistoryID`, `LeaseID`, `User`, `SignDate`)
VALUES
(1, 1, 1000, '2020-01-05'),
(2, 1, 1001, '2020-01-04'),
(3, 1, 1001, '2020-01-02'),
(4, 1, 1000, '2020-01-03'),
(6, 2, 1002, '2020-05-01'),
(7, 2, 1002, '2020-05-03')
;

我正在寻找一个Mysql Update来更新表租约:基于SignDate和User的NoteDate和WhoSignID其中用户签名日期的最小值

表更新后的租期

LeaseID | Name | Users           | WhoSignID | NoteDate 
1       | AAA  | 1000,1001       | 1001      | 2020-01-02
2       | BBB  | 1002            | 1002      | 2020-05-01
...

感谢你的帮助

Answer 1

您的Lease表有一个严重的设计问题，因为它将用户存储为CSV列表。相反，您应该将每个用户值放在单独的记录中。话虽如此，CSV用户列表似乎对您当前的问题无关紧要，它只需要找到每个租约的最早日期。如果是这样，那么一个简单的更新连接就足够了:

UPDATE Lease l
INNER JOIN
(
SELECT h1.LeaseID, h1.User, h2.MinSignDate
FROM History h1
INNER JOIN
(
SELECT LeaseID, MIN(SignDate) AS MinSignDate
FROM History
GROUP BY LeaseID
) h2
ON h2.LeaseID = h1.LeaseID AND
h2.MinSignDate = h1.SignDate
) h
ON h.LeaseID = l.LeaseID
SET
WhoSignID = h.User,
NoteDate = h.MinSignDate;