删除有重复项的记录，并连接到另一个表

Question

我需要写一个查询(Microsoft SQL Server)来删除表中有Vehicle的重复项。CarId =汽车。CarId和具有相同连接(CarId, CounterLimit, km)。

表Car:

CarId
-----
11111

TableVehicle:

VehicleId     CarId      CounterLimit     Kilometers   
-----------------------------------------------------
1             11111               250         120000
2             23456               300         150000
3             11111               250         120000 (record duplicated with 1, should be deleted)

你能帮帮我吗?

Answer 1

删除车牌号较低的行

delete v
from Vehicle v
where exists (
select 1
from Vehicle v2
where v2.VehicleId > v.VehicleId 
and v2.CarId = v.CarId and v2.CounterLimit = v.CounterLimit and v2.Kilometers = v.Kilometers)

只查询表

select max(vehicleid) vehicleid, carid, CounterLimit, Kilometers
from Vehicle
group by carid, CounterLimit, Kilometers

Answer 2

• 加入表格
• 创建基于卡，计数器限制，公里的排名。如果这三个是相同的，它被认为是重复的。如果您需要在此标准中添加更多或更少的列数，您可以调整此部分
• 接下来，我们只取上面的一行，这意味着我们使用rank_1 = 1
消除重复项

with rank as (
select
vehicle.vehicleid,
vehicle.carid,
vehicle.CounterLimit,
vehicle.Kilometers,
row_number() over(partition by vehicle.carid,vehicle.CounterLimit, vehicle.Kilometers order by vehicle.vehicleid) as rank_
from a vehicle
left join car
on Vehicle.CarId =car.carid
)
select * from rank where rank_ = 1