将游标转换为基于 SET 的操作的 T-SQL 问题

Question

基本上我有一个不是我写的光标，但需要一些时间来处理，我想通过一起摆脱光标来尝试和改进它。

代码如下：

DECLARE @class_id int, @title_code varchar(30)

DECLARE title_class CURSOR FOR
SELECT DISTINCT title_code FROM tmp_business_class_titles (NOLOCK)

OPEN title_class

    FETCH title_class INTO @title_code

    WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT TOP 1 @class_id = bc1.categoryid
            FROM tmp_business_class_titles bct, 
            dbo.Categories bc1 (nolock) 
            join dbo.Categories bc2 (nolock) on bc2.categoryid = bc1.highercategoryid
            join dbo.Categories bc3 (nolock) on bc3.categoryid = bc2.highercategoryid 
            WHERE bc1.categoryid = bct.class_id
                AND title_code = @title_code
            ORDER BY Default_Flag DESC

            UPDATE products 
            SET subcategoryid = @class_id 
            WHERE ccode = @title_code 
                AND spdisplaytype = 'Table'

            UPDATE products
            SET subcategoryid = @class_id
            WHERE highercatalogid IN (
                SELECT catalogid FROM products (nolock)
                WHERE ccode = @title_code AND spdisplaytype = 'Table')

            FETCH title_class INTO @title_code
        END

CLOSE title_class

DEALLOCATE title_class

表 tmp_business_class_titles 如下所示：

class_id,title_code,Default_flag

7,101WGA,0

7,10315,0

29,8600,0

默认标志始终可以为 0，但如果为 1，则逻辑应自动选择该 title_id 的默认 class_id。

因此，当前逻辑在游标中循环遍历上表，然后为每个标题选择前 1 个类 id，按默认标志排序（因此应始终首先返回 default_flag 为 1 的 class_id。）并应用默认值class_id 添加到产品表中。

这段代码大约需要 1 分 20 秒才能运行，我试图将其转换为一两个更新语句，但这样做已经耗尽了我的大脑。

如果这可能的话，任何 TSQL 大师都有任何想法，或者我应该重新评估默认标志如何工作的整个逻辑吗？

为任何帮助干杯。

Answer 1

我没有足够的信息可供使用，因此以下查询可能会失败。我特别需要有关产品表的更多信息才能完成这项工作，但假设您有 SQL Server 2005 或更高版本，这可能足以让您朝着正确的方向开始。它使用公共表表达式以及 RANK 函数。我强烈建议学习它们，并且十有八九会大大提高查询的效率。

;WITH cteTitle As (
    SELECT 
        sequence = RANK() OVER (PARTITION BY bct.title_code ORDER BY Default_Flag desc)
        ,bct.title_code
        ,bc1.categoryid
    FROM
        tmp_business_class_titles bct
        join Categories bc1 ON bc1.categoryid = bct.class_id
        join Categories bc2 ON bc2.categoryid = bc1.highercategoryid
        join Categories bc3 ON bc3.categoryid = bc2.highercategoryid
 )                            
 UPDATE 
     prod
 SET
     subcategoryid = ISNULL(t.categoryid,t2.categoryid)
 FROM
     products prod
     LEFT join products subprod ON subprod.catalogid = prod.highercatalogid
     LEFT join cteTitle t ON prod.ccode = t.title_code AND t.sequence = 1 AND prod.spdisplaytype = 'Table'
     LEFT join cteTitle t2 ON subprod.ccode = t2.title_code And t2.sequence = 1 AND subprod.spdisplaytype = 'Table'
 WHERE
     t2.categoryid IS NOT NULL