求将版本映射逻辑的TVF实现改写为单SELECT语句的SQL方案

需求背景 #

• 存在交叉引用表，包含FieldId、SupplierFieldId、TemplateName、TemplateVersion四列
• 每个模板的TemplateVersion=0为通用映射，特定版本的专属映射可通过复制通用映射并修改版本号生成

业务逻辑 #

查询指定TemplateName和TemplateVersion的映射需包含：

1. 该指定版本的所有映射记录
2. 同模板下版本0的通用映射，但需排除那些在指定版本中已有对应FieldId的记录

现有实现 #

当前通过表值函数(TVF)实现，传入@version和@template变量，使用分支判断+UNION拼接查询结果，希望改写为单个SELECT语句。

解决方案 #

利用窗口函数ROW_NUMBER()对每个FieldId按版本优先级排序，优先保留指定版本的记录，若不存在则保留版本0的记录，同时过滤匹配模板名称的数据，实现单语句查询：

Declare @tbl As Table(
                         FieldId         Int        Not Null
                       , SupplierFieldId Int        Not Null
                       , TemplateName    Varchar(6) Not Null
                       , TemplateVersion Int        Null
                       ,
                       Unique(
                                 FieldId
                               , SupplierFieldId
                               , TemplateName
                               , TemplateVersion
                             )
                     );

Declare @version Int = 16;
Declare @template Varchar(6) = 'Small';

Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(1, 2, 'Big', 0);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(1, 3, 'Big', 16);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(2, 4, 'Small', 0);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(3, 5, 'Small', 0);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(3, 5, 'Big', 0);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(4, 5, 'Big', 15);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(4, 7, 'Small', 16);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(2, 4, 'Small', 14);

-- 单语句查询实现
WITH RankedMappings AS (
    SELECT 
        FieldId,
        SupplierFieldId,
        TemplateVersion,
        -- 给记录排序：指定版本排第1，版本0排第2，其他版本排除
        ROW_NUMBER() OVER (
            PARTITION BY FieldId 
            ORDER BY CASE WHEN TemplateVersion = @version THEN 1 
                          WHEN TemplateVersion = 0 THEN 2 
                          ELSE 3 END
        ) AS rn
    FROM @tbl
    WHERE 
        TemplateName = @template
        AND (TemplateVersion = @version OR TemplateVersion = 0)
)
SELECT FieldId, SupplierFieldId, TemplateVersion
FROM RankedMappings
WHERE rn = 1;

结果验证 #

• 当@version=0、@template='Small'时：仅返回Small模板版本0的所有映射
• 当@version=16、@template='Small'时：返回Small模板版本16的映射（FieldId=4）+ 版本0中未被覆盖的映射（FieldId=2、3）
• 当@version=16、@template='Big'时：返回Big模板版本16的映射（FieldId=1）+ 版本0中未被覆盖的映射（FieldId=3）

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