Access SQL查询优化：按优先级筛选Resource下的Unique ID行

针对同一Resource下存在多个Unique ID的场景，我们可以通过优先级打分+关联筛选的方式，确保每个Resource仅返回符合优先级规则的单行数据（Active > Inactive > Withdrawn）。以下是两种适配Access SQL语法的实现方案：

方案1：保留同优先级的所有行（若存在） #

该方案会返回同一Resource下所有优先级最高的行（比如多个Active状态的行），适合允许同优先级多行存在的场景：

SELECT t.*
FROM YourTableName t
INNER JOIN (
    SELECT 
        Resource,
        MAX(
            Switch(
                [Employee Status] = 'Active', 3,
                [Employee Status] = 'Inactive', 2,
                [Employee Status] = 'Withdrawn', 1
            )
        ) AS MaxPriority
    FROM YourTableName
    GROUP BY Resource
) AS pri
ON t.Resource = pri.Resource
AND Switch(
    t.[Employee Status] = 'Active', 3,
    t.[Employee Status] = 'Inactive', 2,
    t.[Employee Status] = 'Withdrawn', 1
) = pri.MaxPriority

语法兼容说明 #

如果你的Access版本不支持Switch函数，可替换为IIf嵌套写法：

SELECT t.*
FROM YourTableName t
INNER JOIN (
    SELECT 
        Resource,
        MAX(
            IIf([Employee Status] = 'Active', 3,
                IIf([Employee Status] = 'Inactive', 2,
                    IIf([Employee Status] = 'Withdrawn', 1, 0)
                )
            )
        ) AS MaxPriority
    FROM YourTableName
    GROUP BY Resource
) AS pri
ON t.Resource = pri.Resource
AND IIf(t.[Employee Status] = 'Active', 3,
        IIf(t.[Employee Status] = 'Inactive', 2,
            IIf(t.[Employee Status] = 'Withdrawn', 1, 0)
        )
    ) = pri.MaxPriority

方案2：强制每个Resource仅返回一行 #

若需要确保同一Resource下无论有多少同优先级行，仅返回其中一行，可使用子查询+TOP 1的方式：

SELECT DISTINCT
    t1.Resource,
    -- 按需添加需要返回的字段，每个字段对应一个子查询
    (SELECT TOP 1 [Unique ID] 
     FROM YourTableName t2 
     WHERE t2.Resource = t1.Resource 
     ORDER BY Switch(t2.[Employee Status]='Active',3,t2.[Employee Status]='Inactive',2,t2.[Employee Status]='Withdrawn',1) DESC) AS [Unique ID],
    (SELECT TOP 1 [Employee Status] 
     FROM YourTableName t2 
     WHERE t2.Resource = t1.Resource 
     ORDER BY Switch(t2.[Employee Status]='Active',3,t2.[Employee Status]='Inactive',2,t2.[Employee Status]='Withdrawn',1) DESC) AS [Employee Status],
    (SELECT TOP 1 [Email] 
     FROM YourTableName t2 
     WHERE t2.Resource = t1.Resource 
     ORDER BY Switch(t2.[Employee Status]='Active',3,t2.[Employee Status]='Inactive',2,t2.[Employee Status]='Withdrawn',1) DESC) AS [Email]
FROM YourTableName t1

使用注意事项 #

• 将所有YourTableName替换为你的实际表名；
• 方案2中需根据需求手动添加所有需要返回的字段，每个字段都要对应一个相同排序规则的子查询；
• 若存在除三种状态外的其他Employee Status值，可在打分逻辑中设为0（或更低分值），确保不会被优先选中。

内容的提问来源于stack exchange，提问作者Gabriel Covello