Azure SQL删除UserSubscriptions表含冗余嵌套引用的用户记录方案

核心规则 #

• 同一UserId下，同一个订阅选项（OptionId）如果关联了多个订阅，仅保留订阅编号（SubscriptionId）最小的对应记录，其余冗余记录全部删除
• 完全匹配你提供的3个业务场景要求

操作步骤 #

第一步：先查询确认待删除的记录（避免误删） #

WITH UserSubscriptionOptions AS (
    -- 关联查询得到每个用户订阅对应的选项ID、所属订阅ID
    SELECT 
        us.Id AS UserSubscriptionId,
        us.userid,
        us.SubscriptionsOptionsId,
        so.OptionId,
        so.SubscriptionId
    FROM UserSubscriptions us
    JOIN SubscriptionsOptions so ON us.SubscriptionsOptionsId = so.Id
),
RankRecords AS (
    -- 按用户+选项分组，订阅ID从小到大排序，序号为1的是要保留的记录
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY userid, OptionId ORDER BY SubscriptionId ASC) AS rn
    FROM UserSubscriptionOptions
)
-- 输出所有待删除的记录，核对无误后再执行删除操作
SELECT * FROM RankRecords WHERE rn > 1;

第二步：执行删除操作 #

WITH UserSubscriptionOptions AS (
    SELECT 
        us.Id AS UserSubscriptionId,
        us.userid,
        us.SubscriptionsOptionsId,
        so.OptionId,
        so.SubscriptionId
    FROM UserSubscriptions us
    JOIN SubscriptionsOptions so ON us.SubscriptionsOptionsId = so.Id
),
RankRecords AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY userid, OptionId ORDER BY SubscriptionId ASC) AS rn
    FROM UserSubscriptionOptions
)
DELETE us
FROM UserSubscriptions us
JOIN RankRecords rr ON us.Id = rr.UserSubscriptionId
WHERE rr.rn > 1;

执行结果验证 #

执行删除后查询UserSubscriptions表：

SELECT userid, SubscriptionsOptionsId AS SubscriptionOptionId 
FROM UserSubscriptions 
ORDER BY userid, SubscriptionsOptionsId;

得到的结果和你预期的完全一致：

内容的提问来源于stack exchange，提问作者OTUser