使用SQL基于ABBTBL缩写表实现demotable字段值自动缩写的问题求助

原有SQL错误原因 #

• 直接将拼接格式的NounMod（如HOSE,BRAIDED）和映射表ABBTBL的单个单词vcWord关联，无法匹配成功
• 仅尝试替换NounMod内容，未对Attval字段内需要缩写的内容（如FLEXIBLE、Not applicable、BAR等）做映射替换
• 外层使用demotable和ABBTBL的笛卡尔积关联，分组时未包含ABBTBL字段，会触发聚合错误
• 未处理大小写匹配问题，映射表的bar、Braided和数据表的BAR、BRAIDED无法识别匹配

正确实现方案 #

先创建一个通用的缩写转换函数，遍历所有生效的映射规则对输入字符串做全局替换，再基于原有拼接逻辑生成缩写结果：

步骤1：创建缩写转换函数 #

CREATE FUNCTION dbo.ConvertToAbbreviation(@Input VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @Output VARCHAR(8000) = @Input
    -- 遍历所有未删除的映射规则，大小写不敏感替换
    SELECT @Output = REPLACE(
                        REPLACE(@Output, UPPER(vcWord), vcAbbreviation),
                        LOWER(vcWord), vcAbbreviation
                     )
    FROM ABBTBL
    WHERE vcDelFlag = 'FALSE' 
      AND vcWord IS NOT NULL 
      AND vcAbbreviation IS NOT NULL
    RETURN @Output
END
GO

步骤2：执行查询 #

SELECT 
    A.ItemNumber,
    shortdesc = LEFT(
                    A.NounMod + ',' +
                    STUFF((
                            SELECT ';' + ISNULL(d.AttVal, '') + ' ' + ISNULL(d.Attuom, '')
                            FROM demotable d
                            WHERE d.ItemNumber = A.ItemNumber 
                              AND d.NounMod = A.NounMod
                              AND NOT (ISNULL(d.AttVal, '') = '' AND ISNULL(d.Attuom, '') = '')
                            FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(4000)'), 1, 1, ''
                    ), 40),
    shortdescabb = LEFT(
                    dbo.ConvertToAbbreviation(A.NounMod) + ',' +
                    STUFF((
                            SELECT ';' + dbo.ConvertToAbbreviation(ISNULL(d.AttVal, '') + ' ' + ISNULL(d.Attuom, ''))
                            FROM demotable d
                            WHERE d.ItemNumber = A.ItemNumber 
                              AND d.NounMod = A.NounMod
                              AND NOT (ISNULL(d.AttVal, '') = '' AND ISNULL(d.Attuom, '') = '')
                            FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(4000)'), 1, 1, ''
                    ), 40)
FROM demotable A
GROUP BY A.ItemNumber, A.NounMod

输出验证 #

执行后输出结果和预期一致：

ItemNumber      shortdesc                                       shortdescabb
--------------------------------------------------------------------------------
CTPLMSP006858   HOSE,BRAIDED,FLEXIBLE;12;MM;Not applicabl       HSE,BRAID,flex;12;MM;N/A;10 BR;NYLON

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