我有一个零件库存表,该表按PartName,WarehouseId,VendorCode(主要关注列)存储零件。根据WarehouseId和VendorCode,它仅应具有唯一的PartName条目。但是,条目混合了,在这种情况下,我需要获取PartName,WarehouseId和Vendor。例如:

ABC133, Warehouse 10, VendorCode 1234
ABC133, Warehouse 10, VendorCode 1222

BBB111, Warehouse 20, VendorCode 1111
BBB111, Warehouse 20, VendorCode 2222


我已经自定义了在此站点上找到的查询以执行此操作,但是它只为每个重复的PartName带来第一个“重复”,并且我需要获取所有错误的条目:

ABC133, Warehouse 10, VendorCode 1222

BBB111, Warehouse 20, VendorCode 1111


这是我使用的查询:

SELECT i.MFGPN, i.VendorCode, i.WarehouseID FROM edi_846_inventory i
INNER JOIN (SELECT MFGPN FROM edi_846_inventory
GROUP BY MFGPN HAVING count(MFGPN) > 1 and count(VendorCode) > 1) dup ON i.MFGPN = dup.MFGPN
where MFGPN is the PartName


谢谢

最佳答案

这是您想要的查询:

SELECT i.MFGPN, i.VendorCode, i.WarehouseID
FROM edi_846_inventory i INNER JOIN
     (SELECT MFGPN, WarehouseID
      FROM edi_846_inventory
      GROUP BY MFGPN, WarehouseID
      HAVING count(*) > 1
     ) dup
     ON i.MFGPN = dup.MFGPN AND i.WarehouseID = dup.WarehouseID;


换句话说,您的子查询需要同时按MFGPNWarehouseID进行聚合。

此外,仅将供应商连接在一起可能就足够了:

      SELECT MFGPN, WarehouseID, GROUP_CONCAT(VendorCode) as Vendors
      FROM edi_846_inventory
      GROUP BY MFGPN, WarehouseID
      HAVING count(*) > 1

09-28 14:15