Excel ODBC连接LEFT JOIN含重复表报错的解决方案求助

问题根源 #

你遇到的两个核心问题：

• 关联条件里的子查询 (select DISTINCT(SUPPLIER_SIZES) from tblMapSizes) 会返回多行结果，用=匹配自然触发"Subquery returned more than 1 value"错误；
• tblMapSizes中SUPPLIER_SIZES没有唯一约束，直接关联会导致主表每条记录匹配多条映射表记录，最终结果重复。

解决方案 #

我们需要调整关联逻辑，同时处理映射表的重复数据。这里提供两种可靠的方案：

方案1：先对映射表去重再关联

如果SUPPLIER_SIZES对应多个SIZES但你只需要任意一个，或者确保去重后SUPPLIER_SIZES唯一，可以先通过子查询对tblMapSizes去重，再和主表关联：

SELECT DISTINCT -- 可选：如果主表本身也有重复，保留DISTINCT
       c.GROUP_NO, 
       c.GROUP_NAME, 
       c.DEPT_NAME, 
       c.CLASS_NAME, 
       ISNULL(c.BRAND, c.CLASS_NAME) as BRAND, 
       c.MAIN_SEASON, 
       c.SUB_SEASON, 
       c.SUB_NAME, 
       ISNULL(c.PRODUCT_TYPE, c.SUB_NAME) as PRODUCT_TYPE, 
       ISNULL(c.PL_CYCLE, 'New/Continuity') as PL_CYCLE, 
       c.ITEM, 
       CAST(c.ITEM as int) as ITEM_VALUE, 
       c.ITEM_DESC, 
       c.ITEM_PARENT, 
       CAST(c.ITEM_PARENT as int) as ITEM_PARENT_VALUE, 
       c.VPN, 
       c.SUPP_COLOUR, 
       c.ARNOTTS_COLOUR, 
       c.SIZE_1, 
       size.SIZES as SIZE_WEB, 
       c.SIZE_2, 
       c.RETAIL_PRICE, 
       c.EAN, 
       CAST(c.EAN as bigint) as EAN_VALUE, 
       c.WH_SOH, 
       c.STOCK_ON_HAND, 
       ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END) as "WEB_ID", 
       case when row_number() over (partition by ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),c.ITEM_PARENT) order by (select 1)) > 1 then 0 else 1 end as OPTION_COUNT, 
       CASE WHEN SUM(c.STOCK_ON_HAND) OVER (PARTITION BY c.ITEM_PARENT) > 0 THEN 'YES' ELSE 'NO' END as IN_STOCK, 
       SUM(c.STOCK_ON_HAND) OVER (PARTITION BY c.ITEM_PARENT) as STYLE_SOH, 
       CASE WHEN c.PL_CYCLE = 'Discontinued' OR CHARINDEX('Dum', c.CLASS_NAME) > 0 OR b.WEB_ALLOWED = 'No' THEN 0 ELSE 1 END as CONGRUENCY, 
       ISNULL(b.CUTOUTS,'DN') as USE_COUTOUTS, 
       CASE WHEN c.ITEM_PARENT = CASE WHEN w.ITEM = c.ITEM THEN c.ITEM_PARENT ELSE NULL END THEN c.ITEM_PARENT ELSE NULL END as MARKED_FOR_WEB_WEB_ID, 
       CASE WHEN w.CREATE_DATETIME IS NOT NULL THEN 'YES' ELSE 'NO' END AS MARKED_FOR_WEB_SKU, 
       w.CREATE_DATETIME, 
       CASE WHEN odi.SKU_B4N_UPLOAD_MODIFIED_DATE IS NOT NULL THEN 'YES' ELSE 'NO' END AS PUBLISHED, 
       cast(odi.SKU_B4N_UPLOAD_MODIFIED_DATE as datetime) as PD, 
       CASE WHEN odi.SKU_B4N_UPLOAD_MODIFIED_DATE IS NOT NULL THEN 1 ELSE 0 END AS PUBLISHED_COUNT, 
       CASE WHEN odi.SKU_B4N_UPLOAD_MODIFIED_DATE IS NULL AND co.COPY_COMPLETE_DATE IS NOT NULL AND amp.IMAGE_DATE_UPLOADED IS NOT NULL THEN 'YES' ELSE 'NO' END AS READY_TO_UPLOAD, 
       CASE WHEN amp.IMAGE_DATE_UPLOADED IS NOT NULL THEN 'YES' ELSE 'NO' END AS IMG_UPLOADED, 
       amp.IMAGE_DATE_UPLOADED as IUD, 
       co.COPY_COMPLETE_DATE, 
       CASE WHEN co.COPY_COMPLETE_DATE IS NOT NULL THEN 'YES' ELSE 'NO' END AS COPY_COMP, 
       CASE WHEN tr."CREATE DATE" IS NOT NULL THEN 'YES' ELSE 'NO' END as TRANSFERRED_TO_PACKSHOT, 
       tr."CREATE DATE" as TTPD, 
       CASE WHEN io.DATE IS NOT NULL THEN 'YES' ELSE 'NO' END as IMAGE_ORDER, 
       io.DATE as IOD, 
       wcid.ONLINE_FLAG as DW_ONLINE_FLAG_WCID, 
       wcid.PUBLISHED as DW_PUBLISHED_FLAG_WCID, 
       REPLACE(ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END),'_','') as REPLACED 
FROM tblCrystal c 
LEFT JOIN tblODIPublishedCSV odi ON c.ITEM = odi.SKU 
LEFT JOIN tblWebIDLegacy l ON c.ITEM = l.SkuId 
-- 修改这里：先对tblMapSizes按SUPPLIER_SIZES去重，再关联
LEFT JOIN (
    SELECT DISTINCT SUPPLIER_SIZES, SIZES 
    FROM tblMapSizes
) size ON c.SIZE_1 = size.SUPPLIER_SIZES 
LEFT JOIN tblBrands b on CAST(c.GROUP_NO AS VARCHAR(10)) + '_' + c.BRAND=b.PRIMARY_KEY 
LEFT JOIN tblMarkedForWeb w on c.ITEM=w.ITEM 
LEFT JOIN tblDWCopy co on ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END) = co.WEB_ID 
LEFT JOIN tblAmplianceReport amp on ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END) = amp.WEB_ID 
LEFT JOIN tblTransfers tr on c.ITEM=tr.SKU 
LEFT JOIN tblImageOrder io on c.ITEM_PARENT=io.ITEM_PARENT 
LEFT JOIN MV_REP_PUBLISHED_WCID_LEVEL wcid on REPLACE(ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END),'_','') = wcid.PRODUCT_ID 
WHERE c.ITEM_PARENT IS NOT NULL 
ORDER BY WEB_ID DESC

方案2：用窗口函数取每个SUPPLIER_SIZES的唯一映射

如果同一个SUPPLIER_SIZES对应多个SIZES，你需要指定规则（比如取最新的、ID最大的）来选择唯一的SIZES，可以用ROW_NUMBER()窗口函数：

SELECT c.GROUP_NO, 
       c.GROUP_NAME, 
       c.DEPT_NAME, 
       c.CLASS_NAME, 
       ISNULL(c.BRAND, c.CLASS_NAME) as BRAND, 
       c.MAIN_SEASON, 
       c.SUB_SEASON, 
       c.SUB_NAME, 
       ISNULL(c.PRODUCT_TYPE, c.SUB_NAME) as PRODUCT_TYPE, 
       ISNULL(c.PL_CYCLE, 'New/Continuity') as PL_CYCLE, 
       c.ITEM, 
       CAST(c.ITEM as int) as ITEM_VALUE, 
       c.ITEM_DESC, 
       c.ITEM_PARENT, 
       CAST(c.ITEM_PARENT as int) as ITEM_PARENT_VALUE, 
       c.VPN, 
       c.SUPP_COLOUR, 
       c.ARNOTTS_COLOUR, 
       c.SIZE_1, 
       size.SIZES as SIZE_WEB, 
       c.SIZE_2, 
       c.RETAIL_PRICE, 
       c.EAN, 
       CAST(c.EAN as bigint) as EAN_VALUE, 
       c.WH_SOH, 
       c.STOCK_ON_HAND, 
       ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END) as "WEB_ID", 
       case when row_number() over (partition by ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),c.ITEM_PARENT) order by (select 1)) > 1 then 0 else 1 end as OPTION_COUNT, 
       CASE WHEN SUM(c.STOCK_ON_HAND) OVER (PARTITION BY c.ITEM_PARENT) > 0 THEN 'YES' ELSE 'NO' END as IN_STOCK, 
       SUM(c.STOCK_ON_HAND) OVER (PARTITION BY c.ITEM_PARENT) as STYLE_SOH, 
       CASE WHEN c.PL_CYCLE = 'Discontinued' OR CHARINDEX('Dum', c.CLASS_NAME) > 0 OR b.WEB_ALLOWED = 'No' THEN 0 ELSE 1 END as CONGRUENCY, 
       ISNULL(b.CUTOUTS,'DN') as USE_COUTOUTS, 
       CASE WHEN c.ITEM_PARENT = CASE WHEN w.ITEM = c.ITEM THEN c.ITEM_PARENT ELSE NULL END THEN c.ITEM_PARENT ELSE NULL END as MARKED_FOR_WEB_WEB_ID, 
       CASE WHEN w.CREATE_DATETIME IS NOT NULL THEN 'YES' ELSE 'NO' END AS MARKED_FOR_WEB_SKU, 
       w.CREATE_DATETIME, 
       CASE WHEN odi.SKU_B4N_UPLOAD_MODIFIED_DATE IS NOT NULL THEN 'YES' ELSE 'NO' END AS PUBLISHED, 
       cast(odi.SKU_B4N_UPLOAD_MODIFIED_DATE as datetime) as PD, 
       CASE WHEN odi.SKU_B4N_UPLOAD_MODIFIED_DATE IS NOT NULL THEN 1 ELSE 0 END AS PUBLISHED_COUNT, 
       CASE WHEN odi.SKU_B4N_UPLOAD_MODIFIED_DATE IS NULL AND co.COPY_COMPLETE_DATE IS NOT NULL AND amp.IMAGE_DATE_UPLOADED IS NOT NULL THEN 'YES' ELSE 'NO' END AS READY_TO_UPLOAD, 
       CASE WHEN amp.IMAGE_DATE_UPLOADED IS NOT NULL THEN 'YES' ELSE 'NO' END AS IMG_UPLOADED, 
       amp.IMAGE_DATE_UPLOADED as IUD, 
       co.COPY_COMPLETE_DATE, 
       CASE WHEN co.COPY_COMPLETE_DATE IS NOT NULL THEN 'YES' ELSE 'NO' END AS COPY_COMP, 
       CASE WHEN tr."CREATE DATE" IS NOT NULL THEN 'YES' ELSE 'NO' END as TRANSFERRED_TO_PACKSHOT, 
       tr."CREATE DATE" as TTPD, 
       CASE WHEN io.DATE IS NOT NULL THEN 'YES' ELSE 'NO' END as IMAGE_ORDER, 
       io.DATE as IOD, 
       wcid.ONLINE_FLAG as DW_ONLINE_FLAG_WCID, 
       wcid.PUBLISHED as DW_PUBLISHED_FLAG_WCID, 
       REPLACE(ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END),'_','') as REPLACED 
FROM tblCrystal c 
LEFT JOIN tblODIPublishedCSV odi ON c.ITEM = odi.SKU 
LEFT JOIN tblWebIDLegacy l ON c.ITEM = l.SkuId 
-- 修改这里：用窗口函数给每个SUPPLIER_SIZES分组，取ID最大的那条记录
LEFT JOIN (
    SELECT SUPPLIER_SIZES, SIZES,
           ROW_NUMBER() OVER (PARTITION BY SUPPLIER_SIZES ORDER BY ID DESC) AS rn
    FROM tblMapSizes
) size ON c.SIZE_1 = size.SUPPLIER_SIZES AND size.rn = 1 
LEFT JOIN tblBrands b on CAST(c.GROUP_NO AS VARCHAR(10)) + '_' + c.BRAND=b.PRIMARY_KEY 
LEFT JOIN tblMarkedForWeb w on c.ITEM=w.ITEM 
LEFT JOIN tblDWCopy co on ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END) = co.WEB_ID 
LEFT JOIN tblAmplianceReport amp on ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END) = amp.WEB_ID 
LEFT JOIN tblTransfers tr on c.ITEM=tr.SKU 
LEFT JOIN tblImageOrder io on c.ITEM_PARENT=io.ITEM_PARENT 
LEFT JOIN MV_REP_PUBLISHED_WCID_LEVEL wcid on REPLACE(ISNULL(ISNULL(odi.WEB_PROD_STYLE_ID,l.WebProductStyleID),CASE WHEN CHARINDEX('Oxfo', c.SIZE_1) = 1 THEN CAST(c.ITEM_PARENT AS VARCHAR(10)) + 'OX' ELSE c.ITEM_PARENT END),'_','') = wcid.PRODUCT_ID 
WHERE c.ITEM_PARENT IS NOT NULL 
ORDER BY WEB_ID DESC

关键说明 #

• 方案1适合SUPPLIER_SIZES和SIZES是一一对应，只是表中有重复数据的场景；
• 方案2适合SUPPLIER_SIZES对应多个SIZES，需要按规则筛选唯一值的场景，你可以修改ORDER BY ID DESC为你需要的排序规则（比如按SIZES排序）；
• 移除了原来错误的子查询关联方式，改成直接匹配SUPPLIER_SIZES，避免了子查询返回多行的报错。

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