SQL Server : Cursor to CTE almost there

Question

我有一个光标，我想将其变成 CTE。

我想避免的是循环逻辑。它的作用是，返回所有特定的“文件夹”，然后循环检查新的folderid = oldfolderid，如果是，则设置DupCheck = 1，否则设置DupCheck = 0。

当它循环时，它不会在第一次folderid更改时将DupCheck设置为1，而是在每个后​​续文件夹上将DupCheck设置为1，直到folderid再次更改。这样，只有该组folderids的第一条记录是DupCheck=0，后面的所有记录都是1(因为它们是重复的)...有意义吗？

我不太能准确地看到解决方案。需要一点帮助。

这是旧光标以及到目前为止我所拥有的新 CTE。

旧代码(光标):

-- old cursor
declare @DFolderId int, @DItemID int
declare @Dname varchar(100)     
declare @DFolderIdNew int
set @DFolderIdNew = 0

declare CurDup cursor for
    select FolderID, EntityName, ItemID  
    from @TempTable 
    where FolderID in (select FolderID 
                       from @TempTable 
                       group by FolderID 
                       having count(*) > 1) 
    order by FolderID, EntityType

open CurDup

fetch next from CurDup into @DFolderId, @Dname, @DItemID

while @@FETCH_STATUS = 0
begin
    if @DFolderIdNew = @DFolderId
    begin
        update @TempTable 
        set DupCheck = 1 
        where FolderID = @DFolderId and ItemID = @DItemID                            
    end
    else 
    begin                             
        update @TempTable 
        set DupCheck = 0 
        where FolderID = @DFolderId and ItemID = @DItemID                          
    end

    set @DFolderIdNew = @DFolderId

    fetch next from CurDup into @DFolderId, @Dname, @DItemID
end

close CurDup
deallocate CurDup

CTE 更换

-- CTE replacement for cursor
;WITH cteCurDup (FolderID, EntityName, EntityType, ItemID, RowNum) AS
(
    SELECT 
        FolderID, EntityName, Entitytype, ItemID, 
        ROW_NUMBER() OVER (ORDER BY FolderID, EntityType) AS RowNum
    FROM 
        @TempTable 
    WHERE 
        FolderID IN (SELECT FolderID 
                     FROM @TempTable 
                     GROUP BY FolderID 
                     HAVING COUNT(*) > 1)
        -- AND RowNum > 1
)
UPDATE @TempTable 
SET DupCheck = 1 
WHERE ItemID IN (SELECT ItemID FROM cteCurDup WHERE RowNum > 1)

Answer 1

你可以使用这个。

;With cteCurDup (FolderID, EntityName, EntityType, ItemID, DupCheck, RowNum, RowCnt )
as 
(
    SELECT FolderID, EntityName, Entitytype, ItemID, DupCheck,
        ROW_NUMBER() OVER (PARTITION BY FolderID ORDER BY EntityType) AS RowNum,
        COUNT(*) OVER (PARTITION BY FolderID ) AS RowCnt
    FROM @TempTable 
)
Update cteCurDup set DupCheck= (CASE WHEN RowNum = 1 THEN 0 ELSE 1 END)
where RowCnt > 1