Oracle:使用联接从表中删除组中的重复项 [英] Oracle: Delete duplicates in a group from Table with join

问题描述

期望输出为:

我想按组删除表中的重复项.

I want to delete duplicates from a table by a group.

我的桌子:

rc_document:

+----------------+-------------+----------------------+
| rc_document_id | document_id | rc_document_group_id |
+----------------+-------------+----------------------+
|              1 |           1 |                    1 |
|              2 |           2 |                    1 |
|              3 |           3 |                    1 |
|              4 |           4 |                    1 |
|              5 |           1 |                    2 |
|              6 |           3 |                    2 |
+----------------+-------------+----------------------+

rc_document_group:

+----------------------+----------+
| rc_document_group_id | priority |
+----------------------+----------+
|                    1 |        1 |
|                    2 |        2 |
+----------------------+----------+

我只想保留rc_document_group具有最高优先级的rc_documents.具有相同"document_id"的所有其他条目都应删除. 换句话说，... document_id只能位于优先级最高的rc_document_group中，其他的应该删除

I only want to keep the rc_documents whose rc_document_group has the highest priority. All other entries with the same "document_id" should be deleted. In other words ... document_id should only be in the rc_document_group with the highest priority, the other ones should be deleted

这是我的预期结果:

+----------------+-------------+----------------------+
| rc_document_id | document_id | rc_document_group_id |
+----------------+-------------+----------------------+
|              2 |           2 |                    1 |
|              4 |           4 |                    1 |
|              5 |           1 |                    2 |
|              6 |           3 |                    2 |
+----------------+-------------+----------------------+

推荐答案

使用Oracle的KEEP LAST为每个document_id查找最佳的rc_document_id.然后删除所有其他内容.

Use Oracle's KEEP LAST to find the best rc_document_id per document_id. Then delete all others.

delete from rc_document
where rc_document_id not in
(
  select max(d.rc_document_id) keep (dense_rank last order by dg.priority)
  from rc_document d
  join rc_document_group dg using (rc_document_group_id)
  group by d.document_id
);

Rester演示: http://rextester.com/NZVZGF52818

Rextester demo: http://rextester.com/NZVZGF52818

这篇关于Oracle:使用联接从表中删除组中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！