主页 / dba / 问题 / 120233
Accepted
Juan Velez
Asked: 2015-11-06 10:34:57 +0800 CST

SQL Server - 从子查询/派生表中删除

  • 772

有没有办法可以将下面的SELECT语句变成一个DELETE？

我想从[ETL].[Stage_Claims]表中删除相应的返回记录。

由于我使用了派生表，因此无法引用该Stage_Claims表。

总而言之，以下查询中使用的 2 个物理表具有相同的结构。唯一的区别是DUPS_Claims的一个子集Stage_Claims。

DUPS_Claims包含在 中找到的重复记录Stage_Claims。如果一条记录在 中存在 3 次Stage_Claims，那么我们也会在 中拥有该记录 3 次DUPS_Claims。

Stage_Claims包含所有记录，包括 中的重复记录DUPS_Claims。

我想删除重复记录，以免Stage_Claims为每条重复记录留下 1 条唯一记录。

Stage_Claims仅有 100 万行，所以我不想在整个表上使用 Row_Number / Partition，因为它需要超过 2 分钟才能运行。

下面的查询我运行了大约 15 秒，并且仅成功识别了重复记录（不包括我们要保留的原始唯一记录），但我无法弄清楚如何删除从 SC 返回的记录。

有可能还是我应该采取不同的方法？

SELECT *
FROM (
    SELECT RN = ROW_NUMBER() OVER (
            PARTITION BY SC.ID ORDER BY SC.id
            )
        ,SC.*
    FROM [ETL].[Stage_Claims] SC
    WHERE ID IN (
            SELECT ID
            FROM (
                SELECT RN = ROW_NUMBER() OVER (
                        PARTITION BY ID ORDER BY id
                        )
                    ,ID
                FROM [ETL].[DUPS_Claims]
                ) AS t1
            WHERE RN > 1
            )
    ) AS t2
WHERE RN > 1
sql-server

1 个回答

  1. Best Answer

    将您的 select 语句转换为 CTE 和DELETE FROMCTE，如下所示：

    ;WITH del AS
(
SELECT *
FROM (
    SELECT RN = ROW_NUMBER() OVER (
            PARTITION BY SC.ID ORDER BY SC.id
            )
        ,SC.*
    FROM [ETL].[Stage_Claims] SC
    WHERE ID IN (
            SELECT ID
            FROM (
                SELECT RN = ROW_NUMBER() OVER (
                        PARTITION BY ID ORDER BY id
                        )
                    ,ID
                FROM [ETL].[DUPS_Claims]
                ) AS t1
            WHERE RN > 1
            )
    ) AS t2
WHERE RN > 1
)
DELETE FROM del;

    标准警告：您应该在非生产环境中进行测试。

    您可以大大简化您的查询，并可能通过使用以下不使用中间表的查询获得更好的性能DUPS_Claims，因为它绝对没有必要：

    ;WITH cte AS
(
    SELECT sc.ID
        , rn = ROW_NUMBER() OVER (PARTITION BY sc.ID ORDER BY sc.ID)
    FROM ETL.Stage_Claims sc
)
DELETE
FROM cte 
WHERE rn > 1;

    我在两个表上创建了一个非聚集的、非唯一的索引，然后查看了这两个变体的执行计划。

    第一个变体：

    在此处输入图像描述

    第二种变体：

    在此处输入图像描述

    第一个变体扫描索引两次，而第二个变体显然只需要扫描一次索引，并且在我有点做作的示例中不需要相对昂贵的合并连接。我的示例ETL.Stage_Claims表包含 89 个唯一ID值，每个值重复 89 次，总共 7921 行。

    如果 CTE 不是你的东西，你可以使用这种方法从派生表中删除，而不是：

    DELETE c
FROM (
    SELECT sc.ID
        , rn = ROW_NUMBER() OVER (PARTITION BY sc.ID ORDER BY sc.ID)
    FROM ETL.Stage_Claims sc
) c
WHERE rn > 1;

    DELETE以上从派生表的查询计划：

    在此处输入图像描述

    • 13

相关问题