来自相关TOP 1记录的SQL UPDATE行

Question

我有一个包含两列的表Products：ProductID, ProductName。

当我销售这些产品时，我将销售额存储在两张表中：

• SalesHeaders，有3列：IDHeader, EmployeeName, Date
• 3列SalesRows：IDHeader, IDRow, ProductID

有两名员工，约翰和玛丽。

现在，我需要在表Products中添加一个名为LastMarySaleDate的新列。所以我想更新Products.LastMarySaleDate，为此我需要从SalesHeaders中选择最新的记录，其中EmployeeName = 'mary'和SalesRows.ProductID = Products.ProductID。

以下是预期结果的样本数据

产品(更新前(：

ProductID  ProductName  LastMarySaleDate
--------- ------------  ----------------
A01        Mouse
A02        Keyboard
A03        Speakers

SalesHeaders:

IDHeader  EmployeeName  Date
--------  ------------  ----------
1         Mary          2020-05-01
2         Mary          2020-05-02
3         John          2020-05-03

SalesRows:

IDHeader  IDRow  ProductID
--------  -----  ---------
1         1      A01
1         2      A02
2         3      A01
3         4      A02
3         5      A03

产品(更新后(：

ProductID  ProductName  LastMarySaleDate
--------- ------------  ----------------
A01        Mouse        2020-05-02
A02        Keyboard     2020-05-01
A03        Speakers     Note: Empty, since Mary never sold this productID

我试过

UPDATE Products
SET Products.LastMarySaleDate = H.Date
FROM 
(SELECT TOP 1 *
FROM SalesHeaders 
LEFT OUTER JOIN SalesRows ON SalesHeaders.IDHeader = SalesRows.IDHeader
WHERE SalesHeaders.EmployeeName = 'Mary' 
AND SalesRows.ProductID = Products.ProductID
ORDER BY SalesHeaders.Date DESC) AS H

但我想不通。如果有人能帮我，那就太好了，谢谢！

Answer 1

我想这就是您想要的：

CREATE TABLE #Product (ProductID varchar(10),   ProductName varchar(20),  LastMarySaleDate DATE)
INSERT INTO #Product
VALUES ('A01','Mouse', NULL),
('A02','Keyboard',NULL),
('A03','Speakers',NULL)

CREATE TABLE #SalesHeader(IDHeader INT,  EmployeeName varchar(20), DT  Date)
INSERT INTO #SalesHeader VALUES (1,'Mary','2020-05-01'),
(2,'Mary','2020-05-02'),
(3,'John','2020-05-03')

CREATE TABLE #SalesRows(IDHeader int,  IDRow int,  ProductID varchar(10))
INSERT INTO #SalesRows VALUES(1,1,'A01'),
(1,2,'A02'),
(2,3,'A01'),
(3,4,'A02'),
(3,5,'A03')

UPDATE #Product
SET LastMarySaleDate = t2.DT
FROM #Product t1
INNER JOIN 
(
SELECT ProductID, MAX(dt) DT 
FROM #SalesHeader t1
INNER JOIN #SalesRows t2 on t1.IDHeader = t2.IDHeader
WHERE t1.EmployeeName = 'Mary'
GROUP BY ProductID
) t2 on t1.ProductID = t2.ProductID