在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..

推荐答案

UPDATE Folder  
SET DefaultPhotoID = FirstPhoto
FROM (SELECT FolderID AS FID, MIN(PhotoID) AS FirstPhoto
      FROM Gallery
      GROUP BY FolderID) AS g 
WHERE FolderID = FID

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查询中需要帮助。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！