SQL将最新数据拉入另一个表

Question

我有两个表相机与列- [Id, Name]和camerimages与列- [CamerId(FK)， DateAdded, FileLocation]。我有一个系统，我分配了一堆相机和新的相机图像存储每天。我需要添加一个新的列到相机call [LatestCameraImageLocation]。我还需要编写一个sql脚本来循环通过我现有的cameraiages，为每个相机找到最新的CamerImage，并将cameraiimage中的FileLocation写入相机中的LatestCameraImageLocationColumn． 我已经尝试了一堆方法，包括cte，但似乎不能让它工作。

Answer 1

可以这样使用not exists:

Select c.*, ci.filelocation as latestcameraimage
From camera c 
Join cameraimage ci on c.id = ci.cameraid
Where not exists
(Select 1 from cameraimage cii 
Where cii.camerid = ci.cameraid
And cii.dateadded > ci.dateadded)

Answer 2

根据这是什么，它可能值得重新考虑你的模型。但严格来说

UPDATE Camera
FROM (SELECT CameraID,
FileLocation
FROM (SELECT FileLocation,
CameraID,
ROW_NUMBER() OVER 
( PARTITION BY cameraID
ORDER BY dateAdded DESC
) rn
FROM CameraImages
)
WHERE rn = 1
) UPDT
SET Camera.LatestCameraImageLocation = UPDT.fileLocation
WHERE Camera.ID = UPDT.CameraID;

如果是我，我会考虑创建一个视图，因为否则这个更新将需要在CameraImage发生变化时运行。视图方法将保持同步，无需干预。

CREATE VIEW CAMERA_LOCATION AS 
SELECT cam.id,
ci.FileLocation
FROM Camera cam
INNER
JOIN
(SELECT FileLocation,
CameraID,
ROW_NUMBER() OVER 
( PARTITION BY cameraID
ORDER BY dateAdded DESC
) rn
FROM CameraImages
) ci
ON cam.id = ci.cameraID
AND rn = 1;