基于多列删除重复项

Question

我使用以下方式列出了重复项：

select s.MessageId, t.* 
from Message s
join (
    select ToUserId, FromUserId, count(*) as qty
    from Message
    group by ToUserId, FromUserId
    having count(*) > 1
) t on s.ToUserId = t.ToUserId and s.FromUserId = t.FromUserId

现在，我该如何删除除一条消息以外的所有消息（我想要去重，这样我才能在FromUserId和ToUserId上应用唯一索引）。

- SB2055

---

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - FuzzyTree

@FuzzyTree，如果已经有有用的答案了，为什么要投票关闭呢？ - SB2055

因为这仍然是一个重复的问题，重复的问题会使网站混乱，对未来的访问者没有任何价值。 - FuzzyTree

@FuzzyTree请看您提供的链接。问题和答案都很混乱。重复的意图并不等同于重复的结果 - 现在我们有一个更易访问的资源。 - SB2055

Answer 1

使用cte并为每行分配行号，以便删除除重复对中的一个外的所有行。

with rownums as 
(select m.*, 
 row_number() over(partition by ToUserId, FromUserId order by ToUserId, FromUserId) as rnum
 from Message m)
delete r
from rownums r
where rnum > 1

- Vamsi Prabhala

---

我们也可以使用DENSE_RANK()而不是ROW_NUMBER。 - Peter

Answer 2

生成样本数据

    DECLARE @Message TABLE(ID INT ,ToUserId varchar(100),FromUserId varchar(100))
    INSERT INTO @Message(ID,ToUserId, FromUserId )
    VALUES  ( 1,'abc',  'def'  ), ( 2,'abc',  'def'  ), ( 3,'abc',  'def'  ), ( 4,'qaz',  'xsw'  )

--删除数据

    DELETE m FROM @Message AS m 
    INNER JOIN (
         SELECT *,row_number()OVER(PARTITION BY ToUserId,FromUserId ORDER BY ID ) AS rn FROM @Message AS m
    ) t ON t.ID=m.ID
    WHERE t.rn>1

    SELECT * FROM @Message

----------- ---------- ----------
1           abc        def
4           qaz        xsw

如果没有用于指定行ID的列，则可以尝试使用行地址（例如%% lockres %%）

    DELETE m FROM @Message AS m 
    INNER JOIN (
        SELECT *,row_number()OVER(PARTITION BY ToUserId,FromUserId ORDER BY %%lockres%% ) AS rn FROM @Message AS m
    ) t ON t.ID=m.ID
    WHERE t.rn>1

    SELECT *, %%lockres%% FROM @Message

- Nolan Shang