我试图避免在这个解决方案中使用循环,而是使用基于集合的操作。
我需要根据最近出现的连接到另一个表的情况来更新表中的每条记录。我的第一个想法是在连接条件上连接到需要“最近的日期”from和group的表,但是使用带有UPDATE
的GROUP BY
是无效的。
这是我到目前为止所掌握的。我有预感这个问题会通过CTE解决的。
CREATE TABLE Part (
Area VARCHAR(10) NOT NULL,
SequenceNumber INT NOT NULL,
DateAdded DATETIME NOT NULL
);
INSERT INTO Part VALUES
('A', 1, '2018-04-01'),
('A', 1, '2018-04-02'),
('A', 2, '2018-04-03'),
('A', 2, '2018-04-04'),
('B', 2, '2018-04-04'),
('B', 2, '2018-04-03'),
('B', 3, '2018-04-02'),
('B', 3, '2018-04-01');
DECLARE @Filters TABLE
(
Area VARCHAR(10),
StartingSeqNum INT,
StartTime DATETIME
);
INSERT INTO @Filters VALUES
('A', 2, NULL),
('B', 3, NULL);
-- Update each StartTime in @Filters with the most recent
-- DateAdded of the corresponding SequenceNumber in the Part table per Area.
UPDATE f
SET f.StartTime = MAX(p.DateAdded)
FROM
@Filters f
INNER JOIN Part p ON p.Area = f.Area
AND p.SequenceNumber = f.StartingSeqNum
GROUP BY
p.Area,
p.SequenceNumber;
根据上面的示例数据,该操作应在@Filters
中填充以下值
('A', 2, '2018-04-04')
('B', 3, '2018-04-02')
发布于 2018-04-21 08:10:24
(a)
UPDATE Filters
SET Filters.StartTime = (SELECT max(Part.DateAdded)
FROM Part
WHERE Part.Area = Filters.Area
AND Part.SequenceNumber = Filters.StartingSeqNum)
FROM @Filters Filters;
(b)
UPDATE Filters
SET Filters.StartTime = X.StartTime
FROM @Filters Filters
INNER JOIN (SELECT max(Part.DateAdded) StartTime,
Part.Area,
Part.SequenceNumber
FROM Part
GROUP BY Part.Area,
Part.SequenceNumber) X
ON X.Area = Filters.Area
AND X.SequenceNumber = Filters.StartingSeqNum;
(a)和(b)都应该做你想做的事情。我不知道还有什么更好。也许(b)可以重用子查询关系,因此总体成本更低。
https://stackoverflow.com/questions/49950941
复制相似问题