sql - 通过确定分类值的优先级来删除重复项

Question

我的数据库中有一些条目的分类字段已更新，但旧值未被删除。我只想按照我定义的顺序保留具有“最高”分类值的条目。

输入:

id | degree
=============
1  | Diploma
1  | Bachelors
1  | Masters
2  | Diploma
2  | Bachelors  

输出(期望):

id | degree
=============
1  | Masters
2  | Bachelors  

我最后尝试使用具有“最高”值的 CASE 来覆盖以前的值，但它没有按预期工作。

SELECT
    DISTINCT id,
    (CASE
        WHEN id = 'Diploma'   THEN 'Diploma'
        WHEN id = 'Bachelors' THEN 'Bachelors'
        WHEN id = 'Masters'   THEN 'Masters'
    END) as degree
FROM
    academic_record

这个操作有方法吗？

Answer 1

使用 case 进行排序是正确的想法。我会查询每个 id 的 rank，然后取前几位:

SELECT id, degree
FROM   (SELECT id,
               degree,
               RANK() OVER (PARTITION BY id
                            ORDER BY CASE degree WHEN 'Diploma'   THEN 1
                                                 WHEN 'Bachelors' THEN 2
                                                 WHEN 'Masters'   THEN 3
                                     END DESC) AS rn
        FROM   academic_record) t
WHERE rn = 1