sql - 删除重复记录并加入另一个表

Question

我需要编写一个查询（Microsoft SQL Server）来删除表 Vehicle 中具有 Vehicle.CarId = Car.CarId 并且具有相同连接（CarId、CounterLimit、Kilometers）的重复项。

表Car：

CarId
-----
11111

表Vehicle：

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

请你帮助我好吗？

标签： sqlsql-server

Answer 1

删除 VehicleId 较小的行

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