基于层级优先级逻辑从SQL表查询价格的实现及性能疑问

需求背景 #

需要根据CompanyId、EmployeeId、TaskTypeId三个字段，按以下优先级规则查找对应Price：

• 优先级1：三者完全匹配的记录；
• 优先级2：CompanyId和EmployeeId匹配，TaskTypeId为NULL的记录；
• 优先级3：CompanyId匹配，EmployeeId为NULL，TaskTypeId匹配的记录；
• 优先级4：CompanyId匹配，EmployeeId和TaskTypeId均为NULL的记录；
• 优先级5：三者全为NULL的记录；
• 若以上都无匹配，返回默认值@DefaultPrice。

当前使用的COALESCE实现 #

declare @CompanyId INT = 7199
declare @EmployeeId INT = NULL
declare @TaskId INT = NULL
declare @DefaultPrice DECIMAL (9, 2) = 2

select coalesce(
            (select Price from dbo.MyTable where CompanyId = @CompanyId AND EmployeeId = @EmployeeId AND TaskTypeId = @TaskId),
            (select Price from dbo.MyTable where CompanyId = @CompanyId AND EmployeeId = @EmployeeId AND TaskTypeId IS NULL),
            (select Price from dbo.MyTable where CompanyId = @CompanyId AND EmployeeId IS NULL AND TaskTypeId = @TaskId),
            (select Price from dbo.MyTable where CompanyId = @CompanyId AND EmployeeId IS NULL AND TaskTypeId IS NULL),
            (select Price from dbo.MyTable where CompanyId IS NULL AND EmployeeId IS NULL AND TaskTypeId IS NULL),
            @DefaultPrice
        )

性能问题分析 #

你当前的写法存在明显性能隐患：COALESCE会从左到右逐个执行子查询，直到找到第一个非NULL结果。如果前几个子查询都没有命中，就会多次扫描MyTable——极端情况下会扫6次表。当表数据量较大，或者没有合适索引时，这种写法的性能会急剧下降。

更高效的排序匹配方案 #

推荐使用单表扫描+优先级排序的方式，只扫一次表就能找到目标记录，性能远优于多次子查询。核心思路是给每条潜在匹配的记录计算优先级得分，然后取得分最高的那条。

方案1：基于优先级得分的查询 #

declare @CompanyId INT = 7199
declare @EmployeeId INT = NULL
declare @TaskId INT = NULL
declare @DefaultPrice DECIMAL (9, 2) = 2

SELECT ISNULL(Price, @DefaultPrice) AS FinalPrice
FROM (
    SELECT 
        Price,
        -- 按需求定义优先级得分，分数越高优先级越高
        CASE 
            WHEN CompanyId = @CompanyId AND EmployeeId = @EmployeeId AND TaskTypeId = @TaskId THEN 5
            WHEN CompanyId = @CompanyId AND EmployeeId = @EmployeeId AND TaskTypeId IS NULL THEN 4
            WHEN CompanyId = @CompanyId AND EmployeeId IS NULL AND TaskTypeId = @TaskId THEN 3
            WHEN CompanyId = @CompanyId AND EmployeeId IS NULL AND TaskTypeId IS NULL THEN 2
            WHEN CompanyId IS NULL AND EmployeeId IS NULL AND TaskTypeId IS NULL THEN 1
            ELSE 0
        END AS PriorityScore
    FROM dbo.MyTable
    -- 先过滤出可能符合条件的记录，减少后续计算量
    WHERE (CompanyId = @CompanyId OR CompanyId IS NULL)
      AND (EmployeeId = @EmployeeId OR EmployeeId IS NULL)
      AND (TaskTypeId = @TaskId OR TaskTypeId IS NULL)
) AS RankedRecords
WHERE PriorityScore > 0
ORDER BY PriorityScore DESC
OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY
-- 若没有匹配记录，返回默认值
UNION ALL
SELECT @DefaultPrice
ORDER BY PriorityScore DESC
OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;

方案2：用ROW_NUMBER排序取第一条 #

declare @CompanyId INT = 7199
declare @EmployeeId INT = NULL
declare @TaskId INT = NULL
declare @DefaultPrice DECIMAL (9, 2) = 2

SELECT ISNULL(Price, @DefaultPrice) AS FinalPrice
FROM (
    SELECT 
        Price,
        ROW_NUMBER() OVER (ORDER BY 
            -- 先按字段匹配度排序，匹配的字段权重更高
            CASE WHEN CompanyId = @CompanyId THEN 1 ELSE 0 END DESC,
            CASE WHEN EmployeeId = @EmployeeId THEN 1 ELSE 0 END DESC,
            CASE WHEN TaskTypeId = @TaskId THEN 1 ELSE 0 END DESC,
            -- 字段不匹配时，NULL的优先级高于非NULL的不匹配值
            CASE WHEN CompanyId IS NULL THEN 1 ELSE 0 END DESC,
            CASE WHEN EmployeeId IS NULL THEN 1 ELSE 0 END DESC,
            CASE WHEN TaskTypeId IS NULL THEN 1 ELSE 0 END DESC
        ) AS RowRank
    FROM dbo.MyTable
    WHERE (CompanyId = @CompanyId OR CompanyId IS NULL)
      AND (EmployeeId = @EmployeeId OR EmployeeId IS NULL)
      AND (TaskTypeId = @TaskId OR TaskTypeId IS NULL)
) AS RankedRecords
WHERE RowRank = 1
UNION ALL
SELECT @DefaultPrice
ORDER BY RowRank
OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;

关键优化建议 #

1. 添加复合索引：给MyTable创建包含查询字段的复合索引，让查询直接走索引覆盖，避免回表扫描：

   CREATE NONCLUSTERED INDEX IX_MyTable_PriorityLookup ON dbo.MyTable 
   (CompanyId, EmployeeId, TaskTypeId) 
   INCLUDE (Price);
   

2. 过滤条件前置：通过WHERE子句提前过滤掉不可能匹配的记录，减少后续排序和计算的数据量。
3. 避免多次表扫描：单表扫描+排序的方式，无论匹配情况如何，都只会扫描一次表，性能更稳定。

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