根据标准确定重复项目的优先级

Question

我有一个数据表，其中包含重复的电子邮件地址。每个电子邮件地址都有一个日期、一个颜色(值：“黑色”、“蓝色”或“绿色”)和一个唯一的ID。可能有一组重复的电子邮件地址包含两个以上的电子邮件地址(即我可能有10个相同电子邮件地址的副本)，每组重复的电子邮件地址可能包含与其各自重复集中的相同或不同的颜色。

我的目标是检索具有特定颜色和最大值(日期)的电子邮件地址的is。我想优先颜色(首先“黑色”，然后“蓝色”，然后“绿色”)，然后移动到最大值(日期)，只有当有两个或更多的电子邮件地址在同一重复集有相同的最高要求的颜色。

示例1

ID          Email          Color             Date
1           xyz@xyz.com    Black             01/01/2014
2           xyz@xyz.com    Black             01/31/2014  
3           xyz@xyz.com    Blue              03/31/2015
4           xyz@xyz.com    Green             01/01/2014
5           xyz@xyz.com    Green             01/01/2014

示例2

ID          Email          Color             Date
6           abc@abc.com    Green             12/31/2014
7           abc@abc.com    Green             01/01/2014
8           abc@abc.com    Blue              01/31/2014

在示例1中，我希望选择ID 2，因为这是一组重复电子邮件地址--“黑色”--的最高期望颜色，而我选择的是最大(日期)。

在示例2中，我希望选择ID 8，因为这是一组重复的电子邮件地址的最高期望颜色-“蓝色”。

Answer 1

根据您的要求，您可以使用ROW_NUMBER()为每组重复电子邮件中的每条记录分配优先级号码。然后，在外部查询中，您可以从每个具有最高优先级的组中选择记录：

SELECT ID, Email, Color
FROM (
SELECT ID, Email, Color,
       ROW_NUMBER() OVER (PARTITION BY email 
                          ORDER BY (CASE Color 
                                       WHEN 'Black' THEN 1 
                                       WHEN 'Blue' THEN 2 
                                       ELSE 3 
                                    END),                          
                                    Date DESC) AS rn
FROM emails ) e
WHERE e.rn = 1

SQL Fiddle Demo