我有一个零件库存表,该表按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;
换句话说,您的子查询需要同时按
MFGPN
和WarehouseID
进行聚合。此外,仅将供应商连接在一起可能就足够了:
SELECT MFGPN, WarehouseID, GROUP_CONCAT(VendorCode) as Vendors
FROM edi_846_inventory
GROUP BY MFGPN, WarehouseID
HAVING count(*) > 1