在SQL查询中需要帮助。

本文介绍了在SQL查询中需要帮助。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两张桌子： 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