我有两张桌子: 1.Folder(FOlderID,DefaultPhotoID) 2.Gallery(PhotoID,FolderID) 和我表中的数据显示如下: 文件夹表: - FolderID - DefaultPhotoID 1 - 空白 2 - 空白 图库表: - PhotoID --FolderID 101--1 102--1 103--1 104-- 2 105--2 106--2 现在我需要更新文件夹的DefaultPhotoID列表格首先是Gallery table的PhotoID。这意味着更新后的文件夹表数据看起来像: FolderID - DefaultPhotoID 1--101 2--104 我的尝试: i我正在尝试使用两个表上的连接但它不起作用..
i have two table: 1.Folder(FOlderID,DefaultPhotoID) 2.Gallery(PhotoID,FolderID) and data in my table shows look like: Folder Table:- FolderID--DefaultPhotoID 1--NULL 2--NULL Gallery Table:- PhotoID--FolderID 101--1 102--1 103--1 104--2 105--2 106--2 now i need to update DefaultPhotoID column of folder table by first PhotoID of Gallery table.It means after update folder table data look like: FolderID--DefaultPhotoID 1--101 2--104 What I have tried: i am trying it using join on both table but its not working..
推荐答案试试这个: Try this: UPDATE Folder SET DefaultPhotoID = FirstPhoto FROM (SELECT FolderID AS FID, MIN(PhotoID) AS FirstPhoto FROM Gallery GROUP BY FolderID) AS g WHERE FolderID = FID
这是一个例。查询首先需要为每个folderId标识第一个PhotoId。然后按folderId进行更新。 Here is an example. The query first need to identity the first PhotoId for each folderId. Then do the update by folderId. DECLARE @Folder TABLE (FolderId INT, DefaultPhotoId INT NULL) DECLARE @Gallery TABLE (PhotoId INT, FolderId INT) INSERT INTO @Folder SELECT 1, NULL UNION SELECT 2, NULL INSERT INTO @Gallery SELECT 101, 1 UNION SELECT 102, 1 UNION SELECT 103, 1 UNION SELECT 104, 2 UNION SELECT 105, 2 UNION SELECT 106, 2 ;WITH temp AS ( SELECT f.FolderId, g.PhotoId, ROW_NUMBER() OVER(PARTITION BY f.FolderId ORDER BY PhotoId) AS RowNumber FROM @Folder f JOIN @Gallery g ON f.FolderId = g.FolderId ) -- SELECT * FROM temp WHERE RowNumber = 1 UPDATE f SET f.DefaultPhotoId = t.PhotoId FROM @Folder f JOIn temp t ON f.FolderId = t.FolderId SELECT * FROM @Folder
输出:
Output:
FolderId DefaultPhotoId 1 101 2 104
更多推荐
在SQL查询中需要帮助。
发布评论