sql - 按组条件选择寄存器

Question

假设我有一个包含两列的表 TYPE & COLOR 两列都可以有重复值，但我需要为 TYPE 列和 COLOR 列中的每个值获取一个寄存器根据层次结构 1-蓝色、2-绿色、3-黑色、4-红色和最后 5-白色的值。为了更好地解释我自己，我有图 1 中的表格，而我想要的结果在图 2 中。

我可以使用 MIN() 和 GROUP BY 获取 TYPE 值，但是我如何根据层次结构获取颜色值？

Answer 1

这可能是一种方法。

WITH CTE AS(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY type ORDER BY CASE color WHEN 'blue'  THEN 1
                                                                    WHEN 'green' THEN 2
                                                                    WHEN 'black' THEN 3
                                                                    WHEN 'red'   THEN 4
                                                                    WHEN 'white' THEN 5 END) rn
    FROM SomeTable
)
SELECT type, color
FROM CTE
WHERE rn = 1;