SQL -使用层次结构管理副本

Question

考虑下表

<表类> 客户 类别 键 tbody><<tr>AjaxVIP1宙斯零售2 b赫拉零售3 cAjax零售1

您可以使用row_number函数按大小写排序表达式，如下所示:

Select Customer, Category, Key_
From
(
Select *,
ROW_NUMBER() Over (Partition By Customer Order By 
Case When Category = 'VIP' Then 1 
When Category = 'Corporate' Then 2
When Category = 'Retail' Then 3
-- continue for all categoreis 
Else 17 
End, key_) rn
From table_name
) T
Where rn = 1

如果你可以有一个单独的表来保存每个类别和它的优先级，你可以简化查询如下:

Create table Categories(Category VARCHAR(50), priority INT);
Insert Into Categories Values 
('VIP', 1),
('Corporate', 2),
('Retail', 3); -- list all categories

和查询:

Select Customer, Category, Key_
From
(
Select T.Customer, T.Category, T.Key_,
ROW_NUMBER() Over (Partition By T.Customer Order By C.priority, T.key_) rn
From table_name T LEFT JOIN Categories C
ON T.Category = C.Category
) T
Where rn = 1

看演示。