使用T-SQL解析路径关联三表并将结果插入临时表

需求概述 #

• 查询三张数据表（Table1: PathInfo、Table2: Region、Table3: PartnerInfo）
• 将结果插入临时表，临时表需包含：PathInfo的FileId、Path，Region的PartnerKey，PartnerInfo的Field1、Field2、Field3

核心规则 #

从PathInfo的Path列解析出两个值，按以下规则关联查询：

1. 优先判断第二个解析值是否为有效整数：
   • 是有效整数则用它作为BusinessId查询Region表
   • 不是则使用第一个必为整数的解析值
2. 仅查询Region表中**Region = 'NORTH'且Type = 'WORLD'**的行，获取对应的PartnerKey
3. 用获取到的PartnerKey关联查询PartnerInfo表，得到对应字段值

样例数据表 #

Table1: PathInfo #

FileId     Path
    1    \\companyName\Production\Storage\Data\Connection\106\10149\PROD\
    2    \\companyName\Production\Storage\Data\Connection\1723\3763\PROD\
    3    \\companyName\Production\Storage\Data\Connection\1534\1216\PROD\
    4    \\companyName\Production\Storage\Data\Connection\1534\NotAnId\PROD\
    5    \\companyName\Production\Storage\Data\Connection\1534\OtherPath\PROD\

Table2: Region #

ID  BusinessId  Region  Type    PartnerKey
24  106         NORTH   NATIONAL    23
24  24          EAST    WORLD       23
25  10149       NORTH   NATIONAL    24
26  26          NORTH   NATIONAL    25
27  27          SOUTH   NATIONAL    26
29  29          NORTH   WORLD       28
30  30          EAST    WORLD       29

Table3: PartnerInfo #

PartnerKey   Field1    Field2  Field3
23            AAA       BBB     Alt1
24            DDD       GGG     Alt2
25            XXX       ZZZ     Alt2

已尝试的路径解析代码 #

DECLARE @RootLength VARCHAR(100) = '\\companyName\\Production\\Storage\\Data\\Connection\\'
  
SELECT FileId, Path,
    TRIM('\' from 
      SUBSTRING(Path,LEN(@RootLength)+1,
          CHARINDEX('\' , SUBSTRING(Path,LEN(@RootLength)+1,LEN(@RootLength)))
          )) 
        AS FirstBusinessId,

    SUBSTRING(
    TRIM('\' from
    SUBSTRING(Path,LEN(@RootLength)+LEN(SUBSTRING(Path,LEN(@RootLength)+1,
          CHARINDEX('\' , SUBSTRING(Path,LEN(@RootLength)+1,LEN(@RootLength))
          )) ),LEN(@RootLength))), 0, CHARINDEX('\',
          TRIM ('\' from SUBSTRING(Path,LEN(@RootLength)+LEN(SUBSTRING(Path,LEN(@RootLength)+1,
          CHARINDEX('\' , SUBSTRING(Path,LEN(@RootLength)+1,LEN(@RootLength))
          )) ),LEN(@RootLength)))
          ))
        AS SecondBusinessId
INTO #BusinessIDTemp -- 临时表目标
FROM PathInfo

完整解决方案代码 #

针对原代码的解析逻辑优化，并完成全流程关联查询，最终插入临时表：

-- 声明根路径变量
DECLARE @RootPath NVARCHAR(256) = N'\\companyName\Production\Storage\Data\Connection\';

-- 步骤1：解析Path并确定最终使用的BusinessId
WITH ParsedPath AS (
    SELECT 
        FileId,
        Path,
        -- 解析第一个BusinessId（必为整数）
        TRIM('\' FROM SUBSTRING(Path, LEN(@RootPath) + 1, CHARINDEX('\', SUBSTRING(Path, LEN(@RootPath) + 1)) - 1)) AS FirstBusinessId,
        -- 解析第二个BusinessId
        TRIM('\' FROM SUBSTRING(
            SUBSTRING(Path, LEN(@RootPath) + CHARINDEX('\', SUBSTRING(Path, LEN(@RootPath) + 1)), LEN(Path)),
            1,
            CHARINDEX('\', SUBSTRING(Path, LEN(@RootPath) + CHARINDEX('\', SUBSTRING(Path, LEN(@RootPath) + 1)), LEN(Path))) - 1
        )) AS SecondBusinessId
    FROM PathInfo
),
DeterminedBusinessId AS (
    SELECT 
        FileId,
        Path,
        -- 判断第二个值是否为有效整数，是则用它，否则用第一个
        CASE 
            WHEN ISNUMERIC(SecondBusinessId) = 1 AND SecondBusinessId NOT LIKE '%.%' -- 排除小数，确保是整数
                THEN CAST(SecondBusinessId AS INT)
            ELSE CAST(FirstBusinessId AS INT)
        END AS TargetBusinessId
    FROM ParsedPath
)
-- 步骤2：关联查询Region和PartnerInfo，插入临时表
SELECT 
    d.FileId,
    d.Path,
    r.PartnerKey,
    p.Field1,
    p.Field2,
    p.Field3
INTO #FinalTempTable -- 最终结果临时表
FROM DeterminedBusinessId d
LEFT JOIN Region r 
    ON d.TargetBusinessId = r.BusinessId
    AND r.Region = 'NORTH'
    AND r.Type = 'WORLD'
LEFT JOIN PartnerInfo p 
    ON r.PartnerKey = p.PartnerKey;

-- 可选：查看临时表结果
SELECT * FROM #FinalTempTable;

代码说明 #

1. 路径解析优化：简化了原有的嵌套SUBSTRING逻辑，更易读且减少出错概率
2. 整数有效性判断：用ISNUMERIC结合排除小数的条件，确保第二个解析值是有效整数
3. 关联逻辑：严格按照需求筛选Region表中符合Region=NORTH、Type=WORLD的行，再关联PartnerInfo获取字段
4. 临时表生成：直接通过CTE+SELECT INTO生成最终结果临时表，无需中间临时表（若需保留中间解析表可调整）

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