基于给定值的具有优先级的sql更新

Question

我有下面的carRepair表

car     repair  repair_date   
toyota  breaks  1/3/2018
toyota  motor   1/2/2018
toyota  brakes  1/1/2018
mazada  lights  1/2/2017
mazda   brakes  1/1/2017

我需要添加一个列，其中包括最坏的修理之前的汽车，基于：

最坏的修理

1. 马达
2. 制动器
3. 灯光

就像这样

汽车修理repair_date worst_repair丰田汽车1/3 2018年丰田汽车1/2 2018年丰田汽车1/1/2018年1/1/2018年马自达车灯1/2/2017马自达1/1/2017

目前，我正在逐个进行更新，比如：

 update cr set cr.worst_repair = cr2.repair 
 from 
     carRepair cr inner join 
     carRepair cr2 on 
            cr.car = cr2.car and 
            cr.repair_date >= cr2.repair_date
where
    cr2.repair = 'lights'

然后

 update cr set cr.worst_repair = cr2.repair 
 from 
     carRepair cr inner join 
     carRepair cr2 on 
            cr.car = cr2.car and 
            cr.repair_date >= cr2.repair_date
where
    cr2.repair = 'breaks'

最后

 update cr set cr.worst_repair = cr2.repair 
 from 
     carRepair cr inner join 
     carRepair cr2 on 
            cr.car = cr2.car and 
            cr.repair_date >= cr2.repair_date
where
    cr2.repair = 'motor'

有比一个接一个的方法更有效的方法吗？

为了澄清，断电覆盖灯，马达覆盖所有它们。

谢谢

Answer 1

下面是一个可扩展的解决方案，它使用一个定义不同修复及其严重性的表：

create table repairTypes (severity integer, description varchar(30));
# should make severity unique

insert into repairTypes values (1, 'lights'), (2, 'breaks'), (3, 'motor');

update carRepair cr
set worst_repair = (
  select rt.description from repairTypes rt
  where rt.severity = (
    select max(rt2.severity) as max_severity
     from carRepair cr2 
       inner join repairTypes rt2 on cr2.repair=rt2.description
     where cr2.car=cr.car
     and cr2.repair_date <= cr.repair_date
  )
);

Answer 2

使用TOP 1和Outer在SQL-SERVER中应用并按优先级排序

演示这里http://rextester.com/live/UTOV54697

declare @carRepair table (car varchar(10),repair varchar(20), repair_date date, worst_repair varchar(20) null)

insert into @carRepair (car,repair,repair_date)
values 
('toyota','brakes','1/3/2018'),
('toyota','motor','1/2/2018'),
('toyota','brakes','1/1/2018'),
('mazda','lights','1/2/2017'),
('mazda','brakes','1/1/2017')

select * from @carRepair


update c1
set worst_repair = isnull(x.repair,c1.repair)
from @carRepair c1
outer apply (
select top 1 c2.repair
from @carRepair c2
where c2.car = c1.car
and c1.repair_date >= c2.repair_date
order by
(case c2.repair when 'motor' then 1 when 'brakes' then 2 else 3 end)
) x


select * from @carRepair