基于[CURR MNBR]字段值的SQL条件连接实现需求

需求说明 #

需基于#raw_customer表的[CURR MNBR]字段实现条件LEFT JOIN，规则如下：

• 当[CURR MNBR]为NULL时，连接条件需匹配#merged_products表的[Config] = 'Stock'；
• 当[CURR MNBR]不为NULL时，使用[CURR MNBR] = mp.[MNumber]的匹配逻辑，同时需匹配以下字段：
  • #raw_customer.[DECIMAL] 与 #merged_products.[Thickness]
  • #raw_customer.[WIDTH] 与 #merged_products.[Width]
  • #raw_customer.[LENGTH] 与 #merged_products.[Length]
  • #raw_customer.[SAE] 与 #merged_products.[Grade]
  • 额外匹配：#raw_customer.[AID]需存在于#merged_products.[AID]的分号分隔列表中（针对[CURR MNBR]非NULL的情况）

测试用表结构与数据 #

CREATE TABLE #raw_customer  
(
    [DECIMAL] decimal(20, 4),
    [WIDTH] decimal(20, 4),
    [LENGTH] decimal(20, 4),
    [SAE] varchar(10),
    [CURR MNBR] varchar(255),
    [AID] varchar(255)
);

CREATE TABLE #merged_products 
(
    [pID] int,
    [Thickness] decimal(20, 4),
    [Width] decimal(20, 4),
    [Length] decimal(20, 4),
    [Grade] varchar(10),
    [MNumber] varchar(255),
    [Config] varchar(255),
    [AID] varchar(255)
);

INSERT INTO #raw_customer 
VALUES (0.299,  48, 100, 'XB0', NULL,       ''),
       (0.3,    60, 120, 'XB0', 'M723087',  ''),
       (0.25,   48, 140, 'GR1', 'M701283',  '906008314'),
       (0.25,   60, 140, 'Y45', NULL,       '008008314'),
       (0.125,  72, 205, 'GR7', 'M712390',  '005003468');
 
INSERT INTO #merged_products 
VALUES (1, 0.2990,  48.0000, 100.0000, 'XB0', NULL, 'Stock',     ''),
       (2, 0.3000,  60.0000, 120.0000, 'XB0', NULL,     'Stock', ''),
       (3, 0.2500,  48.0000, 140.0000, 'GR1', 'M701283',    'Stock', ''),
       (4, 0.2500,  60.0000, 140.0000, 'Y45', NULL,     'Raced', ''),
       (5, 0.1250,  72.0000, 205.0000, 'GR7', 'M712390',    'Raced', '005003468; 006008314; '),
       (6, 0.1250,  72.0000, 205.0000, 'GR7', 'M712390',    'Raced', '900751488; 006025951; 006022051');

原尝试SQL语句 #

SELECT
    mp.[pID],
    r.[DECIMAL],    mp.[Thickness],
    r.[WIDTH],      mp.[Width],
    r.[LENGTH],     mp.[Length],
    r.[SAE],        mp.[Grade],
    mp.[Config],    
    r.[CURR MNBR],  mp.[MNumber],
    r.[AID],        mp.[AID]
FROM 
    #raw_customer r
LEFT JOIN 
    #merged_products mp ON
    CAST(r.[DECIMAL] AS decimal(20,4))  = CAST(mp.[Thickness] AS decimal(20,4)) AND
    CAST(r.[WIDTH] AS decimal(20,4))    = CAST(mp.[Width] AS decimal(20,4)) AND
    CAST(r.[LENGTH] AS decimal(20,4))   = CAST(mp.[Length] AS decimal(20,4)) AND
    mp.[Config] = (
        CASE
            WHEN [CURR MNBR] IS NOT NULL THEN  'Stock'
            ELSE '' -- Would want this to accept any value
        END ) AND
    r.[SAE] = mp.[Grade]
GROUP BY
    mp.[pID], mp.[Thickness], mp.[Width], mp.[Length], mp.[Grade], mp.[MNumber], mp.[Config], mp.[AID], 
    r.[DECIMAL], r.[WIDTH], r.[LENGTH], r.[SAE], r.[CURR MNBR], r.[AID]
ORDER BY 
    r.[AID]

期望查询结果 #

|pID    |DECIMAL|Thickness|WIDTH|Width   |LENGTH| Length    |SAE    |Grade  |Config  |CURR MNBR |MNumber  |AID          |AID
|1      | 0.299 |   0.2990| 48  | 48.0000| 100  | 100.0000  | 'XB0' | 'XB0' | 'Stock'|NULL      |NULL     | ''          |NULL|
|2      | 0.3   |   0.3000| 60  | 60.0000| 120  | 120.0000  | 'XB0' | 'XB0' | 'Stock'|'M723087' |NULL     | ''          |NULL|
|3      | 0.25  |   0.2500| 48  | 48.0000| 140  | 140.0000  | 'GR1' | 'GR1' | 'Stock'|'M701283' |'M701283'| '906008314' |NULL|
|4      | 0.25  |   0.2500| 60  | 60.0000| 140  | 140.0000  | 'Y45' | 'Y45' | 'Raced'|NULL      |NULL     | '008008314' |NULL|
|6      | 0.125 |   0.1250| 72  | 72.0000| 205  | 205.0000  | 'GR7' | 'GR7' | 'Raced'|'M712390' |'M712390'| '005003468' |'005003468; 006025951; 006022051'|

正确SQL实现方案 #

原SQL的问题在于：

1. CASE语句处理Config的逻辑完全错误，混淆了两种场景的条件；
2. 缺少[CURR MNBR]与MNumber的匹配逻辑，以及AID的列表包含判断；
3. 无意义的GROUP BY语句，不需要聚合操作却强行分组。

修正后的SQL如下：

SELECT
    mp.[pID],
    r.[DECIMAL],
    mp.[Thickness],
    r.[WIDTH],
    mp.[Width],
    r.[LENGTH],
    mp.[Length],
    r.[SAE],
    mp.[Grade],
    mp.[Config],
    r.[CURR MNBR],
    mp.[MNumber],
    r.[AID],
    mp.[AID]
FROM #raw_customer r
LEFT JOIN #merged_products mp ON
    -- 基础字段匹配（字段类型一致，无需CAST）
    r.[DECIMAL] = mp.[Thickness]
    AND r.[WIDTH] = mp.[Width]
    AND r.[LENGTH] = mp.[Length]
    AND r.[SAE] = mp.[Grade]
    -- 分场景处理连接条件
    AND (
        -- 场景1：CURR MNBR为NULL时，匹配Config='Stock'
        (r.[CURR MNBR] IS NULL AND mp.[Config] = 'Stock')
        -- 场景2：CURR MNBR非NULL时，匹配MNumber+AID列表包含
        OR (
            r.[CURR MNBR] IS NOT NULL
            AND mp.[MNumber] = r.[CURR MNBR]
            AND (
                r.[AID] = '' 
                OR mp.[AID] LIKE '%' + r.[AID] + '%'
            )
        )
    )
ORDER BY r.[AID]

关键修正点 #

• 移除不必要的CAST操作，两张表的对应数值字段类型一致，直接相等即可；
• 使用逻辑分支替代CASE，清晰区分两种场景的连接条件；
• 针对[CURR MNBR]非NULL的情况，补充MNumber匹配逻辑，同时处理AID的分号列表包含判断；
• 删除无意义的GROUP BY语句，避免错误过滤数据。

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