LEFT JOIN关联tbl_item导致查询结果重复问题求助

问题描述 #

使用LEFT JOIN tbl_item AS i ON sd.ItemIDsale = i.itemid关联tbl_item表后，查询结果出现重复记录；移除该关联语句及对应的i.itemnm, i.itmcod, i.itmdescription字段后，结果显示正常。

原查询语句：

SELECT
    sd.saleinvid, sd.slno, 
    i.itemnm, i.itmcod, i.itmdescription,
    h.hsnnumber, sd.sDQty, sd.sDMRP, sd.sDRate, sd.sdschmP,
    sd.sdschmA, sd.sDdis, sd.SDdisA, sd.sdcgst, sd.scgstamt,
    sd.sdsgst, sd.ssgstamt, sd.sdigst, sd.sigstamt, sd.sTaxableAmt, 
    sd.stotaltaxamt, sd.sDAmount, sd.ItemIDsale, sd.S_ItemHSNid,
    SD.S_bid, IB.btch_no, c.catnm, sc.subcatnm                                       
FROM
    tbl_sale_invdet AS sd                                           
left JOIN 
    tbl_item AS i ON sd.ItemIDsale = i.itemid
INNER JOIN 
    tbl_HSN_Master AS h ON sd.S_ItemHSNid = h.HsnId
INNER JOIN 
    tblcat AS c ON c.catid = sd.ItemIDsale  -- i.ITMcatid
INNER JOIN 
    tblsubcat AS sc ON sc.subcatid = sd.ItemIDsale  -- i.ITMsubcatid
LEFT JOIN 
    tbl_item_btch AS IB ON IB.btch_id = SD.S_bid
WHERE
    saleinvid = 'XX/S-00103/25-26'   
ORDER BY
    sd.slno  

已做测试：

DECLARE @itbl TABLE 
              (
                  itemnm varchar (255),
                  itmcod varchar (255),
                  itmdescription varchar (255)
              )

INSERT INTO @itbl (itemnm, itmcod, itmdescription) 
    SELECT
        itemnm, itmcod, itmdescription 
    FROM
        tbl_item 
    WHERE
        itemid IN (SELECT ItemIDsale FROM tbl_sale_invdet 
                   WHERE saleinvid = 'XX/S-00103/25-26')

SELECT * FROM @itbl

可能的重复原因分析 #

1. tbl_item表存在重复itemid记录 #

你的测试只查询了item名称、编码等字段，未验证itemid是否重复。执行以下语句确认：

SELECT itemid, COUNT(*) AS record_count
FROM tbl_item
WHERE itemid IN (SELECT ItemIDsale FROM tbl_sale_invdet WHERE saleinvid = 'XX/S-00103/25-26')
GROUP BY itemid
HAVING COUNT(*) > 1;

如果返回结果，说明同一个itemid对应多条tbl_item记录，LEFT JOIN时会将每条匹配记录关联到主表，导致主表记录重复。

2. 分类表关联逻辑错误引发笛卡尔积 #

你注释掉了原本应使用的i.ITMcatid和i.ITMsubcatid，当前用sd.ItemIDsale关联tblcat和tblsubcat：

• 如果sd.ItemIDsale与c.catid、sc.subcatid并非一一对应关系，INNER JOIN会产生笛卡尔积，再结合tbl_item的重复记录，会进一步放大重复数。

3. JOIN顺序导致的隐性重复 #

LEFT JOIN tbl_item后紧跟的INNER JOIN会过滤掉不匹配的记录，但如果tbl_item存在重复itemid，结合后续关联会让主表记录被多次匹配。

建议修复步骤 #

1. 修复分类表关联逻辑，将错误的关联条件改回基于tbl_item的分类ID：

INNER JOIN tblcat AS c ON c.catid = i.ITMcatid
INNER JOIN tblsubcat AS sc ON sc.subcatid = i.ITMsubcatid

2. 若tbl_item存在重复itemid，清理重复记录，或在关联时去重：

LEFT JOIN (
    SELECT DISTINCT itemid, itemnm, itmcod, itmdescription
    FROM tbl_item
) AS i ON sd.ItemIDsale = i.itemid

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