sql - sql server 根据优先级删除记录

Question

明细表

name age sports
john 27  football
john 27  cricket 
john 27  basketball
kyle 28  baseball
kyle 28  football
nick 24  football
nick 24  hockey
ron  35  football

优先级表：

name       priority
futsal     1
basketball 2
football   3
cricket    4
baseball   5
hockey     6

desired table :    

name age sports 
john 27  basketball  --as basketball has the highest priority for john
kyle 28  baseball
nick 24  football
ron  35  football

想要的是我只想保留 1 条记录作为名称，其余的应该根据运动的优先级删除。我知道要删除重复的记录，但谁能建议我这可以在 sql server 中完成以及如何！

标签： sqlsql-server

Answer 1

您可以使用窗口函数。这是一种方法：

select dp.*
from (select d.*, min(p.priority) over (partition by d.name)
      from details d join
           priorities p
           on d.sport = p.name
     ) dp
where seqnum = 1;