删除行以在Oracle表中每个组具有最多x行 [英] Delete rows to have maximum x lines per group in Oracle table

问题描述

一个例子是最好的解释. 假设我有下表:PersonCar(人与汽车的关联n x n)

An exemple is the best explanation. Let's say I have the following table : PersonCar (association n x n of persons and cars)

╔════════════╦═════════════╗  
║ PersonId   ║ CarId       ║  
╠════════════╬═════════════╣  
║ 1          ║ 1           ║  
║ 1          ║ 2           ║  
║ 1          ║ 3           ║  
║ 2          ║ 4           ║  
║ 2          ║ 5           ║  
╚════════════╩═════════════╝  

如果我按PersonId将此表分组，则ID = 1的人将关联3辆车. 我想删除表PersonCar的行，以使最多2个与一个人相关的汽车.我不在乎从协会中删除哪辆车.

If I group this table by PersonId, the person with Id = 1 have 3 cars associated.
I want to delete rows of the table PersonCar to have maximum 2 cars associated to a person. I don't care about which car is deleted from the association.

这是一个例子.实际上，我有一个很大的测试表，上面放置了太多的关联(用于负载测试)，现在，我想通过放置X关联最大值来进行清理.

It's an exmple. In reality, I have a big test table on which I put too much association (for load tests), and now, I want to clean by putting X association max.

这是一个oracle数据库.

It's an oracle database.

谢谢您的帮助.

推荐答案

假设组合(person_id, car_id)在表中是唯一的，则可以执行以下操作:

Assuming the combination (person_id, car_id) is unique in the table, you can do something like this:

delete from car_assignment 
where (person_id, car_id) 
        in (select person_id, car_id
            from (
              select person_id, 
                     car_id, 
                     row_number() over (partition by person_id order by car_id) as rn
              from car_assignment
            ) t 
            where rn > 2);

这篇关于删除行以在Oracle表中每个组具有最多x行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！